package com.realtyManage.springboot.Dao;


import com.realtyManage.springboot.Model.Do.CustomerRecord;
import com.realtyManage.springboot.Model.Do.CustomerRegistration;
import com.realtyManage.springboot.Model.Vo.CustomReEntity;
import com.realtyManage.springboot.Model.Vo.ReceptionEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.swing.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

@Repository
public interface CustomerRegistrationRepository extends JpaRepository<CustomerRegistration, Integer>, JpaSpecificationExecutor<CustomerRegistration> {
    @Override
    CustomerRegistration getOne(Integer id);

    @Query(value ="select * from customer_registration where phone = ?1 LIMIT 1",nativeQuery = true)
    CustomerRegistration findByPhone(String phone);

    @Query(value ="select * from customer_registration where id = ?1 LIMIT 1",nativeQuery = true)
    CustomerRegistration queryCustomerRecordById(Integer id);

    @Query("select id from CustomerRegistration where name like %?1%")
    List<String> findByNameLike(String houseName);

    @Query(value = "select name from house_detail where id = ?1",nativeQuery = true)
    String getName(Integer id);

    @Query(value = "select manage_house_id from user where id = ?1",nativeQuery = true)
    String getId(String id);

    @Query(value = "select id from customer_registration where name = ?1",nativeQuery = true)
    String getGName(String name);

    @Query(value = "SELECT * FROM customer_registration WHERE come_time LIKE  %?1% and is_system != '1' ",nativeQuery = true)
    List<CustomerRegistration> queryCountCustom(String yar);

    @Query(value = "SELECT * FROM customer_registration where is_system != '1' ",nativeQuery = true)
    List<CustomerRegistration> queryCountCustomS();

    @Query(value = "select type,content from customer_record where custom_id = ?1 AND content != ''",nativeQuery = true)
    List queryCustomerRecordList(Integer cid);

    @Query(value = "SELECT id,`name`,`status`,`level` FROM `user` a WHERE a.`status` = 1 AND a.`level` IN (2,4) AND a.`name` IS NOT NULL",nativeQuery = true)
    List querySalesperson();

    @Query(value = "select * from customer_registration where  come_time like %?1% ",nativeQuery = true)
    List<CustomerRegistration> findByLikeTime(String thisTime);

    @Query(value = "SELECT * FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 ",nativeQuery = true)
    List<String> queryCountComeSalespersonS(String sTime,String eTime);

    @Query(value = "SELECT LEFT(come_time,10) k ,COUNT(*) AS v FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2" +
            " and reception_id = ?3  GROUP BY LEFT(come_time,10) ",nativeQuery = true)
    List<Object> queryCountComeSalespersonE(String sTime,String eTime,String uid);

    @Query(value = "SELECT LEFT(come_time,10) k ,COUNT(*) AS v FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2  GROUP BY LEFT(come_time,10) ",nativeQuery = true)
    List<Object> queryKv(String sTime,String eTime);

    @Query(value = "select count(1) from customer_registration where  come_time like %?1% AND is_system = 0 ",nativeQuery = true)
    int queryCountCustomNum(String time);

    @Query(value = "select count(1) from customer_registration where  come_time like %?1% AND reception_id = ?2 AND is_system = 0 ",nativeQuery = true)
    int queryCountCustomNumE(String time,String uid);

    @Query(value = "SELECT LEFT(come_time,7) k ,COUNT(*) AS v FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 GROUP BY LEFT(come_time,7) ",nativeQuery = true)
    List<Object> queryCountComeSalespersonMonth(String sTime,String eTime);

    @Query(value = "SELECT LEFT(come_time,7) k ,COUNT(*) AS v FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2" +
            " and reception_id = ?3 GROUP BY LEFT(come_time,7) ",nativeQuery = true)
    List<Object> queryCountComeSalespersonMonthE(String sTime,String eTime,String uid);

    @Query(value = " SELECT COUNT(1) num,area_name FROM customer_registration " +
            " WHERE is_system = 0 AND come_time BETWEEN ?1 and  ?2 " +
            " GROUP BY area_name",nativeQuery = true)
    List<String> queryCountIntention(String sTime,String eTime);

    @Query(value = " SELECT COUNT(1) num,area_name FROM customer_registration " +
            " WHERE is_system = 0 AND come_time BETWEEN ?1 and  ?2 AND reception_id = ?3" +
            " GROUP BY area_name",nativeQuery = true)
    List<String> queryCountIntentionE(String sTime,String eTime,String uid);

    @Query(value = " SELECT COUNT(1) num,area_need FROM customer_registration " +
            " WHERE is_system = 0 AND come_time BETWEEN ?1 and  ?2 AND reception_id = ?3" +
            " GROUP BY area_need",nativeQuery = true)
    List<String> queryCountAreaNeedE(String sTime,String eTime,String uid);

    @Query(value = " SELECT COUNT(1) num,area_need FROM customer_registration " +
            " WHERE is_system = 0 AND come_time BETWEEN ?1 and  ?2 " +
            " GROUP BY area_need",nativeQuery = true)
    List<String> queryCountAreaNeed(String sTime,String eTime);

    @Query(value = " SELECT * FROM((SELECT COUNT(1) customerNum FROM customer_registration WHERE reception_id = ?1 AND is_system = 0) A,  " +
            " (SELECT COUNT(1) rcNum FROM customer_registration WHERE reception_id = ?1 AND is_system =0 AND identify = 1) B, " +
            " (SELECT COUNT(1) yxNum FROM customer_registration WHERE reception_id = ?1 AND is_system =0 AND effective = 1) C)",nativeQuery = true)
    List<String> queryComeCustomerNum(Integer uid);


    @Query(value = " SELECT COUNT(1) num ,area_name areaName FROM customer_registration WHERE is_system = 0 AND reception_id = ?2 AND " +
            " come_time LIKE %?1% GROUP BY area_name ",nativeQuery = true)
    List<String> myCustomerAnalysis(String time,Integer uid);


    @Query(value = " SELECT COUNT(1) num ,area_name areaName FROM customer_registration WHERE is_system = 0 AND reception_id = ?1 GROUP BY area_name ",nativeQuery = true)
    List<String> myCustomerAnalysisE(Integer uid);

    @Query(value = " SELECT COUNT(1) num,area_name areaName FROM customer_registration WHERE is_system = 0 AND reception_id = ?3" +
            " AND come_time LIKE %?1% " +
            " AND area_code = ?2 GROUP BY area_name",nativeQuery = true)
    List<String> myComeCustomer(String time,String areaCode,Integer uid);

    @Query(value = " SELECT COUNT(1) num,area_name areaName FROM customer_registration WHERE is_system = 0 AND reception_id = ?2" +
            " AND area_code = ?1 GROUP BY area_name",nativeQuery = true)
    List<String> myComeCustomerS(String areaCode,Integer uid);

    @Query(value = " SELECT COUNT(1) num,area_name areaName FROM customer_registration WHERE is_system = 0 AND reception_id = ?2" +
            " AND come_time LIKE %?1%  GROUP BY area_name",nativeQuery = true)
    List<String> myComeCustomerE(String time,Integer uid);

    @Query(value = " SELECT COUNT(1) num,area_name areaName FROM customer_registration WHERE is_system = 0 AND reception_id = ?1 GROUP BY area_name",nativeQuery = true)
    List<String> myComeCustomerZ(Integer uid);

    @Query(value = " SELECT IFNULL(b.rcNum/a.lfNum*100,0) lfl,IFNULL(c.yxNum/b.rcNum*100,0) rcl,IFNULL(d.ecNum/a.lfNum*100,0) ecl FROM ( " +
            " (SELECT COUNT(1) lfNum FROM customer_registration WHERE reception_id = ?3 AND is_system = 0 AND area_code = ?2 AND come_time LIKE %?1% ) a, " +
            " (SELECT COUNT(1) rcNum FROM customer_registration WHERE reception_id = ?3 AND is_system = 0 AND area_code = ?2 AND identify = 1 AND come_time LIKE %?1% ) b, " +
            " (SELECT COUNT(1) yxNum FROM customer_registration WHERE reception_id = ?3 AND is_system = 0 AND area_code = ?2 AND effective = 1 AND come_time LIKE %?1%) c, " +
            " (SELECT COUNT(1) ecNum FROM customer_record WHERE uid = ?3 AND area_code = ?2 AND info_time LIKE %?1%) d )",nativeQuery = true)
    List<String> myConversionRate(String time,String areaCode,Integer uid);

    @Query(value = "SELECT * FROM(\n" +
            "(SELECT COUNT(1) ldS FROM customer_registration WHERE is_system = 0 AND customer_source = '来电' AND come_time >= ?1 AND come_time <= ?2 AND reception_id = ?3) a,\n" +
            "(SELECT COUNT(1) azlfs FROM customer_registration WHERE is_system = 0 AND customer_source = '来电' AND come_time >= ?1 AND come_time <= ?2 AND visit = 1 AND reception_id = ?3) aa, \n" +
            "(SELECT COUNT(1) lfS FROM customer_registration WHERE reception_id = ?3 AND is_system = 0 AND visit = 1 AND come_time >= ?1 and come_time <= ?2) b,\n" +
            "(SELECT COUNT(1) rcf FROM (\n" +
            "SELECT reception_id FROM customer_registration\n" +
            "WHERE is_system = 0 AND identify = 1 OR large_sum = 1 OR small_sum = 1 OR down_payments = 1 OR deal_payment_type = '全款/放款'\n" +
            "AND come_time >= ?1 AND come_time <= ?2) z WHERE z.reception_id = ?3) c,\n" +
            "(SELECT COUNT(1) cjS FROM house_detail WHERE related_adviser = ?3 AND cj_time >= ?1 and cj_time <= ?2 AND type = 4 AND related_adviser IS NOT NULL) d,\n" +
            "(SELECT COUNT(DISTINCT(custom_id)) efS FROM customer_record WHERE uid = ?3 AND info_time >= ?1 and info_time <= ?2 GROUP BY custom_id HAVING COUNT(custom_id) > 1) e,\n" +
            "(SELECT COUNT(1) hfS FROM customer_record WHERE uid = ?3 AND info_time >= ?1 and info_time <= ?2) f\n" +
            ")",nativeQuery = true)
    List<Object> myConversionRateS(String sTime,String eTime,Integer uid);

    @Query(value = "SELECT * FROM( \n" +
            "(SELECT COUNT(1) ldS FROM customer_registration WHERE is_system = 0 AND customer_source = '来电' AND come_time >= ?1 AND come_time <= ?2) a, \n" +
            "(SELECT COUNT(1) azlfs FROM customer_registration WHERE is_system = 0 AND customer_source = '来电' AND come_time >= ?1 AND come_time <= ?2 AND visit = 1) aa, \n" +
            "(SELECT COUNT(1) lfS FROM customer_registration WHERE is_system = 0 AND visit = 1 AND come_time >= ?1 and come_time <= ?2) b, \n" +
            "(SELECT COUNT(1) rcf FROM (\n" +
            "SELECT reception_id FROM customer_registration\n" +
            "WHERE is_system = 0 AND identify = 1 OR large_sum = 1 OR small_sum = 1 OR down_payments = 1 OR deal_payment_type = '全款/放款'\n" +
            "AND come_time >= ?1 AND come_time <= ?2) z ) c,\n" +
            "(SELECT COUNT(1) cjS FROM house_detail WHERE cj_time >= ?1 and cj_time <= ?2 AND type = 4 AND related_adviser IS NOT NULL) d, \n" +
            "(SELECT COUNT(1) efS FROM (SELECT COUNT(DISTINCT(custom_id))  FROM customer_record WHERE info_time >= ?1 and info_time <= ?2 GROUP BY custom_id HAVING COUNT(custom_id) > 1 ) az ) e, \n" +
            "(SELECT COUNT(1) hfs FROM (SELECT COUNT(1) FROM `customer_record` WHERE info_time >= ?1 and info_time <= ?2 GROUP BY custom_id) ff) f\n" +
            ")",nativeQuery = true)
    List<Object> teamConversionRate(String sTime,String eTime);

    @Query(value = " SELECT IFNULL(b.rcNum/a.lfNum*100,0) lfl,IFNULL(c.yxNum/b.rcNum*100,0) rcl,IFNULL(d.ecNum/a.lfNum*100,0) ecl FROM ( " +
            " (SELECT COUNT(1) lfNum FROM customer_registration WHERE reception_id = ?2 AND is_system = 0 AND area_code = ?1 ) a, " +
            " (SELECT COUNT(1) rcNum FROM customer_registration WHERE reception_id = ?2 AND is_system = 0 AND area_code = ?1 AND identify = 1 ) b, " +
            " (SELECT COUNT(1) yxNum FROM customer_registration WHERE reception_id = ?2 AND is_system = 0 AND area_code = ?1 AND effective = 1) c, " +
            " (SELECT COUNT(1) ecNum FROM customer_record WHERE uid = ?2 AND area_code = ?1) d )",nativeQuery = true)
    List<String> myConversionRateE(String areaCode,Integer uid);

    @Query(value = " SELECT IFNULL(b.rcNum/a.lfNum*100,0) lfl,IFNULL(c.yxNum/b.rcNum*100,0) rcl,IFNULL(d.ecNum/a.lfNum*100,0) ecl FROM ( " +
            " (SELECT COUNT(1) lfNum FROM customer_registration WHERE reception_id = ?1 AND is_system = 0 ) a, " +
            " (SELECT COUNT(1) rcNum FROM customer_registration WHERE reception_id = ?1 AND is_system = 0  AND identify = 1) b, " +
            " (SELECT COUNT(1) yxNum FROM customer_registration WHERE reception_id = ?1 AND is_system = 0  AND effective = 1) c, " +
            " (SELECT COUNT(1) ecNum FROM customer_record WHERE uid = ?1) d )",nativeQuery = true)
    List<String> myConversionRateZ(Integer uid);

    @Query(value = "SELECT * FROM customer_registration WHERE is_show = 0 AND is_system = 0 AND reception_id = ?1 AND effective = 0 " +
            "AND come_time LIKE %?2% AND level = 'A' UNION ALL " +
            "SELECT * FROM customer_registration WHERE is_show = 0 AND is_system = 0 AND reception_id = ?1 AND effective = 0 " +
            "AND come_time LIKE %?3% AND level = 'B' UNION ALL " +
            "SELECT * FROM customer_registration WHERE is_show = 0 AND is_system = 0 AND reception_id = ?1 AND effective = 0 " +
            "AND come_time LIKE %?4% AND level = 'C' UNION ALL " +
            "SELECT * FROM customer_registration WHERE is_show = 0 AND is_system = 0 AND reception_id = ?1 AND effective = 0 " +
            "AND come_time LIKE %?5% AND level = 'D'",nativeQuery = true)
    List<CustomerRegistration> getMyCustomerList(Integer userId,String A,String B,String C,String D);

    @Query(value = "SELECT * FROM customer_registration where is_system = 0",nativeQuery = true)
    List<CustomerRegistration> getTeamCustomerList();

    @Modifying
    @Transactional
    @Query(value = "update customer_registration set come_time = ?1 where phone = ?2",nativeQuery = true)
    void updateCustomerTime(Date time, String phone);

    @Query(value = "SELECT a.newNum dfNum,a.newNum-b.oldNum n,b.oldNum o,c.cj-d.lcj cjn ,d.lcj cjo,c.cj,e.rcNum,f.jdNum,g.sfNum,h.fkfsNum,i.xse FROM( " +
            "(SELECT COUNT(1) newNum FROM `customer_registration` WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND reception_id = ?3) a, " +
            "(SELECT COUNT(1) oldNum FROM customer_record WHERE custom_id IN (SELECT id FROM `customer_registration` WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND reception_id = ?3)) b, " +
            "(SELECT COUNT(1) cj FROM `customer_registration` WHERE come_time BETWEEN ?1 AND ?2 AND effective = 1 AND is_system =0 AND reception_id = ?3) c, " +
            "(SELECT COUNT(1) lcj FROM customer_record WHERE custom_id IN (SELECT id FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND effective = 1 AND is_system =0 AND reception_id = ?3)) d, " +
            "(SELECT COUNT(1) rcNum FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND identify = 1 AND is_system = 0 AND reception_id =?3) e, " +
            "(SELECT COUNT(1) jdNum FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND large_sum = 1 AND reception_id =?3) f, " +
            "(SELECT COUNT(1) sfNum FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND down_payments = 1 AND reception_id =?3) g, " +
            "(SELECT COUNT(1) fkfsNum FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND payment_type IS NOT NULL AND reception_id =?3) h, " +
            "(SELECT SUM(discount_total) xse FROM house_detail WHERE related_party IN (SELECT id FROM customer_registration WHERE effective = 1 AND come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND reception_id =?3) AND type IN (1,3,4)) i)",nativeQuery = true)
    List<String> queryTeamCustomerList(String sTime,String eTime,Integer uid);


    @Query(value = "SELECT b.lfS lf,IFNULL(FORMAT((b.lfS - a.oldS)/b.lfS *100,2),0) xkbfb,b.lfS-a.oldS xks,IFNULL(FORMAT((a.oldS/b.lfs)*100,2),0) lkbfb,a.oldS,\n" +
            "c.cjS,d.ddS,e.sfS,f.xdS,g.qkS,c.cjS cjz,h.rcS,d.ddS ddz,e.sfS sfz,g.qkS qkz,i.xsze,j.hk FROM(\n" +
            "(SELECT COUNT(1) oldS from (SELECT COUNT(DISTINCT(custom_id)) oldS from customer_record WHERE info_time >= ?1 AND info_time <= ?2 AND uid = ?3 group by custom_id having count(custom_id) >=2 ) z) a,\n" +
            "(SELECT COUNT(1) lfS FROM `customer_registration` WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND reception_id = ?3) b,\n" +
            "(SELECT COUNT(1) cjS FROM house_detail WHERE cj_time >= ?1 AND cj_time <= ?2 AND type in (3,4) AND related_adviser = ?3) c,\n" +
            "(SELECT COUNT(1) ddS FROM house_detail WHERE cj_time >= ?1 AND cj_time <= ?2 AND type = 3 AND related_adviser = ?3) d,\n" +
            "(SELECT COUNT(1) sfS FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND down_payments = 1 AND reception_id = ?3) e,\n" +
            "(SELECT COUNT(1) xdS FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND small_sum = 1 AND reception_id = ?3) f,\n" +
            "(SELECT COUNT(1) qkS FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND reception_id = ?3 AND deal_payment_type = '全款/放款') g,\n" +
            "(SELECT COUNT(1) rcS FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND reception_id = ?3 AND identify = 1) h,\n" +
            "(SELECT IFNULL(SUM(total_actual_amount),0) xsze FROM house_detail WHERE cj_time >= ?1 AND cj_time <= ?2 AND type IN (1,3,4) AND related_adviser = ?3) i,\n" +
            "(SELECT SUM(money_back) hk FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND reception_id = ?3 ) j \n" +
            ")",nativeQuery = true)
    ArrayList<String> queryTeamRCustomerList(String sTime,String eTime,Integer uid);




    @Query(value = "SELECT a.newNum dfNum,a.newNum-b.oldNum n,b.oldNum o,c.cj-d.lcj cjn ,d.lcj cjo,c.cj,e.rcNum,f.jdNum,g.sfNum,h.fkfsNum,i.xse FROM( " +
            "(SELECT COUNT(1) newNum FROM `customer_registration` WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0) a, " +
            "(SELECT COUNT(1) oldNum FROM customer_record WHERE custom_id IN (SELECT id FROM `customer_registration` WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0)) b, " +
            "(SELECT COUNT(1) cj FROM `customer_registration` WHERE come_time BETWEEN ?1 AND ?2 AND effective = 1 AND is_system =0) c, " +
            "(SELECT COUNT(1) lcj FROM customer_record WHERE custom_id IN (SELECT id FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND effective = 1 AND is_system =0)) d, " +
            "(SELECT COUNT(1) rcNum FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND identify = 1 AND is_system = 0) e, " +
            "(SELECT COUNT(1) jdNum FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND large_sum = 1) f, " +
            "(SELECT COUNT(1) sfNum FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND down_payments = 1) g, " +
            "(SELECT COUNT(1) fkfsNum FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND payment_type IS NOT NULL) h, " +
            "(SELECT SUM(discount_total) xse FROM house_detail WHERE related_party IN (SELECT id FROM customer_registration WHERE effective = 1 AND come_time BETWEEN ?1 AND ?2 AND is_system = 0) AND type IN (1,3,4)) i)",nativeQuery = true)
    List<String> queryTeamECustomerList(String sTime,String eTime);


    @Query(value = "SELECT b.lfS lf,IFNULL(FORMAT((b.lfS - a.oldS)/b.lfS *100,2),0) xkbfb,b.lfS-a.oldS xks,IFNULL(FORMAT((a.oldS/b.lfs)*100,2),0) lkbfb,a.oldS,\n" +
            " c.cjS,d.ddS,e.sfS,f.xdS,g.qkS,c.cjS cjz,h.rcS,d.ddS ddz,e.sfS sfz,g.qkS qkz,i.xsze,j.hk FROM(\n" +
            "(SELECT COUNT(1) oldS from (SELECT COUNT(DISTINCT(custom_id)) oldS from customer_record WHERE info_time >= ?1 AND info_time <= ?2 group by custom_id having count(custom_id) >=2 )  z) a,\n" +
            "(SELECT COUNT(1) lfS FROM `customer_registration` WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0  ) b,\n" +
            "(SELECT COUNT(1) cjS FROM house_detail WHERE cj_time >= ?1 AND cj_time <= ?2 AND type in (3,4)) c,\n" +
            "(SELECT COUNT(1) ddS FROM house_detail WHERE cj_time >= ?1 AND cj_time <= ?2 AND type = 3) d,\n" +
            "(SELECT COUNT(1) sfS FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND down_payments = 1) e,\n" +
            "(SELECT COUNT(1) xdS FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND small_sum = 1) f,\n" +
            "(SELECT COUNT(1) qkS FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND deal_payment_type = '全款/放款') g,\n" +
            "(SELECT COUNT(1) rcS FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND identify = 1) h,\n" +
            "(SELECT IFNULL(SUM(total_actual_amount),0) xsze FROM house_detail WHERE cj_time >= ?1 AND cj_time <= ?2 AND type IN (1,3,4)  ) i,\n" +
            "(SELECT SUM(money_back) hk FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 ) j" +
            ")",nativeQuery = true)
    ArrayList<String> queryTeamERCustomerList(String sTime,String eTime);


    @Query(value = "SELECT aa.zs,bb.a,IFNULL(FORMAT(bb.a/aa.zs*100,0),0) abfb,cc.b,IFNULL(FORMAT(cc.b/aa.zs*100,0),0) bbfb,dd.c,IFNULL(FORMAT(dd.c/aa.zs*100,0),0) cbfb,ee.d,IFNULL(FORMAT(ee.d/aa.zs*100,0),0) dbfb FROM(\n" +
            "(SELECT COUNT(1) zs FROM customer_registration WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND reception_id = ?3 AND level in (?4)) aa,\n" +
            "(SELECT COUNT(1) a FROM customer_registration WHERE is_system = 0 AND customer_intention = 244 AND come_time BETWEEN ?1 AND ?2 AND reception_id = ?3 AND level in (?4)) bb,\n" +
            "(SELECT COUNT(1) b FROM customer_registration WHERE is_system = 0 AND customer_intention = 245 AND come_time BETWEEN ?1 AND ?2 AND reception_id = ?3 AND level in (?4)) cc,\n" +
            "(SELECT COUNT(1) c FROM customer_registration WHERE is_system = 0 AND customer_intention = 246 AND come_time BETWEEN ?1 AND ?2 AND reception_id = ?3 AND level in (?4)) dd,\n" +
            "(SELECT COUNT(1) d FROM customer_registration WHERE is_system = 0 AND customer_intention = 247 AND come_time BETWEEN ?1 AND ?2 AND reception_id = ?3 AND level in (?4)) ee\n" +
            ")",nativeQuery = true)
    List getUsrIntentionality(String sTime,String eTime,String userId,List types);

    @Query(value = "SELECT aa.zs,bb.a,IFNULL(FORMAT(bb.a/aa.zs*100,0),0) abfb,cc.b,IFNULL(FORMAT(cc.b/aa.zs*100,0),0) bbfb,dd.c,IFNULL(FORMAT(dd.c/aa.zs*100,0),0) cbfb,ee.d,IFNULL(FORMAT(ee.d/aa.zs*100,0),0) dbfb FROM(\n" +
            "(SELECT COUNT(1) zs FROM customer_registration WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND reception_id IN (?3)) aa,\n" +
            "(SELECT COUNT(1) a FROM customer_registration WHERE is_system = 0 AND customer_intention = 244 AND come_time BETWEEN ?1 AND ?2 AND reception_id IN (?3)) bb,\n" +
            "(SELECT COUNT(1) b FROM customer_registration WHERE is_system = 0 AND customer_intention = 245 AND come_time BETWEEN ?1 AND ?2 AND reception_id IN (?3)) cc,\n" +
            "(SELECT COUNT(1) c FROM customer_registration WHERE is_system = 0 AND customer_intention = 246 AND come_time BETWEEN ?1 AND ?2 AND reception_id IN (?3)) dd,\n" +
            "(SELECT COUNT(1) d FROM customer_registration WHERE is_system = 0 AND customer_intention = 247 AND come_time BETWEEN ?1 AND ?2 AND reception_id IN (?3)) ee\n" +
            ")",nativeQuery = true)
    List getUsrIntentionalityE(String sTime,String eTime,List userId);

    @Query(value = "SELECT aa.zs,bb.a,IFNULL(FORMAT(bb.a/aa.zs*100,0),0) abfb,cc.b,IFNULL(FORMAT(cc.b/aa.zs*100,0),0) bbfb,dd.c,IFNULL(FORMAT(dd.c/aa.zs*100,0),0) cbfb,ee.d,IFNULL(FORMAT(ee.d/aa.zs*100,0),0) dbfb FROM(\n" +
            "(SELECT COUNT(1) zs FROM customer_registration WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 ) aa,\n" +
            "(SELECT COUNT(1) a FROM customer_registration WHERE is_system = 0 AND customer_intention = 244 AND come_time BETWEEN ?1 AND ?2 ) bb,\n" +
            "(SELECT COUNT(1) b FROM customer_registration WHERE is_system = 0 AND customer_intention = 245 AND come_time BETWEEN ?1 AND ?2 ) cc,\n" +
            "(SELECT COUNT(1) c FROM customer_registration WHERE is_system = 0 AND customer_intention = 246 AND come_time BETWEEN ?1 AND ?2 ) dd,\n" +
            "(SELECT COUNT(1) d FROM customer_registration WHERE is_system = 0 AND customer_intention = 247 AND come_time BETWEEN ?1 AND ?2 ) ee\n" +
            ")",nativeQuery = true)
    List getUsrIntentionalityZ(String sTime,String eTime);

    @Query(value = "SELECT aa.zs,bb.a,IFNULL(FORMAT(bb.a/aa.zs*100,0),0) abfb,cc.b,IFNULL(FORMAT(cc.b/aa.zs*100,0),0) bbfb,dd.c,IFNULL(FORMAT(dd.c/aa.zs*100,0),0) cbfb,ee.d,IFNULL(FORMAT(ee.d/aa.zs*100,0),0) dbfb FROM(\n" +
            "(SELECT COUNT(1) zs FROM customer_registration WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND reception_id in (?3) AND level in (?4)) aa,\n" +
            "(SELECT COUNT(1) a FROM customer_registration WHERE is_system = 0 AND customer_intention = 244 AND come_time BETWEEN ?1 AND ?2 AND reception_id in (?3) AND level in (?4)) bb,\n" +
            "(SELECT COUNT(1) b FROM customer_registration WHERE is_system = 0 AND customer_intention = 245 AND come_time BETWEEN ?1 AND ?2 AND reception_id in (?3) AND level in (?4)) cc,\n" +
            "(SELECT COUNT(1) c FROM customer_registration WHERE is_system = 0 AND customer_intention = 246 AND come_time BETWEEN ?1 AND ?2 AND reception_id in (?3) AND level in (?4)) dd,\n" +
            "(SELECT COUNT(1) d FROM customer_registration WHERE is_system = 0 AND customer_intention = 247 AND come_time BETWEEN ?1 AND ?2 AND reception_id in (?3) AND level in (?4)) ee\n" +
            ")",nativeQuery = true)
    List getUsrIntentionalityT(String sTime,String eTime,List ids,List types);

    @Query(value = "SELECT aa.zs,bb.a,IFNULL(FORMAT(bb.a/aa.zs*100,0),0) abfb,cc.b,IFNULL(FORMAT(cc.b/aa.zs*100,0),0) bbfb,dd.c,IFNULL(FORMAT(dd.c/aa.zs*100,0),0) cbfb,ee.d,IFNULL(FORMAT(ee.d/aa.zs*100,0),0) dbfb FROM(\n" +
            "(SELECT COUNT(1) zs FROM customer_registration WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2  AND level in (?3)) aa,\n" +
            "(SELECT COUNT(1) a FROM customer_registration WHERE is_system = 0 AND customer_intention = 244 AND come_time BETWEEN ?1 AND ?2 AND level in (?3)) bb,\n" +
            "(SELECT COUNT(1) b FROM customer_registration WHERE is_system = 0 AND customer_intention = 245 AND come_time BETWEEN ?1 AND ?2 AND level in (?3)) cc,\n" +
            "(SELECT COUNT(1) c FROM customer_registration WHERE is_system = 0 AND customer_intention = 246 AND come_time BETWEEN ?1 AND ?2 AND level in (?3)) dd,\n" +
            "(SELECT COUNT(1) d FROM customer_registration WHERE is_system = 0 AND customer_intention = 247 AND come_time BETWEEN ?1 AND ?2 AND level in (?3)) ee\n" +
            ")",nativeQuery = true)
    List getUsrIntentionalityN(String sTime,String eTime,List types);

    @Query(value = "SELECT area_name,COUNT(1) num FROM customer_registration WHERE id IN( \n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3))\n" +
            "AND property_tye IN (?4)) AND area_name IS NOT NULL AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY area_name",nativeQuery = true)
    List getAreaName(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT area_name,COUNT(1) num FROM customer_registration WHERE id IN( \n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3))\n" +
            ") AND area_name IS NOT NULL AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY area_name",nativeQuery = true)
    List getAreaName(String sTime,String eTime,List userId);

    @Query(value = "SELECT area_name,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4) GROUP BY area_name",nativeQuery = true)
    List getAreaNameW(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT area_name,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 GROUP BY area_name",nativeQuery = true)
    List getAreaNameW(String sTime,String eTime,List userId);

    @Query(value = "SELECT a.area_name,SUM(a.num) FROM(\n" +
            "SELECT area_name,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?4))\n" +
            "GROUP BY area_name\n" +
            "UNION ALL\n" +
            "SELECT area_name,COUNT(1) num FROM customer_registration WHERE reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?4) GROUP BY area_name\n" +
            ") a GROUP BY a.area_name",nativeQuery = true)
    List getAreaNameAll(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT a.area_name,SUM(a.num) FROM(\n" +
            "SELECT area_name,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?3))\n" +
            "GROUP BY area_name\n" +
            "UNION ALL\n" +
            "SELECT area_name,COUNT(1) num FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?3) GROUP BY area_name\n" +
            ") a GROUP BY a.area_name",nativeQuery = true)
    List getAreaNameWRYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT area_name,COUNT(1) FROM customer_registration WHERE reception_id in (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY area_name",nativeQuery = true)
    List getAreaNameAll(String sTime,String eTime,List userId);

    @Query(value = "SELECT area_name,COUNT(1) FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY area_name",nativeQuery = true)
    List getAreaNameAllZ(String sTime,String eTime);

    @Query(value = "SELECT area_name,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND reception_id in (?3) AND is_system = 0))\n" +
            "GROUP BY area_name",nativeQuery = true)
    List getAreaNameRW(String sTime,String eTime,List ids);

    @Query(value = "SELECT area_name,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)" +
            "AND property_tye IN (?3))\n" +
            "GROUP BY area_name",nativeQuery = true)
    List getAreaNameWR(String sTime,String eTime,List types);

    @Query(value = "SELECT area_name,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND reception_id in (?3) AND is_system = 0)" +
            "AND property_tye IN (?4))\n" +
            "GROUP BY area_name",nativeQuery = true)
    List getAreaNameRWAll(String sTime,String eTime,List ids,List types);

    @Query(value = "SELECT area_name,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0))\n" +
            "GROUP BY area_name",nativeQuery = true)
    List getAreaNameCAll(String sTime,String eTime);

    @Query(value = "SELECT area_name,COUNT(1) num FROM customer_registration " +
            "WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND reception_id IN (?3) " +
            "GROUP BY area_name",nativeQuery = true)
    List getAreaNameE(String sTime,String eTime,List userId);

    @Query(value = "SELECT area_name,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3) GROUP BY area_name",nativeQuery = true)
    List getAreaNameWRY(String sTime,String eTime,List types);

    @Query(value = "SELECT area_name,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 GROUP BY area_name",nativeQuery = true)
    List getAreaNameZ(String sTime,String eTime);


    @Query(value = "SELECT * FROM (\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 1) a) aa,\n" +
            "(SELECT COUNT(1) efS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 2) b) bb,\n" +
            "(SELECT COUNT(1) dfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) > 2) c) cc)",nativeQuery = true)
    List getComeNum(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT * FROM (\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 1) a) aa,\n" +
            "(SELECT COUNT(1) efS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 2) b) bb,\n" +
            "(SELECT COUNT(1) dfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) > 2) c) cc)",nativeQuery = true)
    List getComeNum(String sTime,String eTime,List userId);

    @Query(value = "SELECT * FROM(\n" +
            "(SELECT COUNT(1) yfS FROM(SELECT custom_id FROM customer_record WHERE custom_id IN (\n" +
            "SELECT id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4) \n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 1) a) aa,\n" +
            "(SELECT COUNT(1) efs FROM(SELECT custom_id FROM customer_record WHERE custom_id IN (\n" +
            "SELECT id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4) \n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 2) b) bb,\n" +
            "(SELECT COUNT(1) dfs FROM(SELECT custom_id FROM customer_record WHERE custom_id IN (\n" +
            "SELECT id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4) \n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) > 2) c) cc\n" +
            ")",nativeQuery = true)
    List getComeNumW(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT * FROM(\n" +
            "(SELECT COUNT(1) yfS FROM(SELECT custom_id FROM customer_record WHERE custom_id IN (\n" +
            "SELECT id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 \n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 1) a) aa,\n" +
            "(SELECT COUNT(1) efs FROM(SELECT custom_id FROM customer_record WHERE custom_id IN (\n" +
            "SELECT id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 \n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 2) b) bb,\n" +
            "(SELECT COUNT(1) dfs FROM(SELECT custom_id FROM customer_record WHERE custom_id IN (\n" +
            "SELECT id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 \n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) > 2) c) cc\n" +
            ")",nativeQuery = true)
    List getComeNumW(String sTime,String eTime,List userId);

    @Query(value = "SELECT SUM(z.yfS),SUM(z.efS),SUM(z.dfS) FROM (\n" +
            "SELECT * FROM (\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 1) a) aa,\n" +
            "(SELECT COUNT(1) efS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 2) b) bb,\n" +
            "(SELECT COUNT(1) dfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) > 2) c) cc\n" +
            ")\n" +
            "UNION ALL\n" +
            "SELECT * FROM (\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT id FROM customer_registration WHERE reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4))\n" +
            "GROUP BY custom_id HAVING COUNT(custom_id) = 1 ) a) aa,\n" +
            "(SELECT COUNT(1) efS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT id FROM customer_registration WHERE reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4))\n" +
            "GROUP BY custom_id HAVING COUNT(custom_id) = 2 ) b) bb,\n" +
            "(SELECT COUNT(1) dfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT id FROM customer_registration WHERE reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4))\n" +
            "GROUP BY custom_id HAVING COUNT(custom_id) > 2 ) cc) cc\n" +
            ")\n" +
            ") z",nativeQuery = true)
    List getComeNumAll(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT SUM(z.yfS),SUM(z.efS),SUM(z.dfS) FROM (\n" +
            "SELECT * FROM (\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?3)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 1) a) aa,\n" +
            "(SELECT COUNT(1) efS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?3)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 2) b) bb,\n" +
            "(SELECT COUNT(1) dfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?3)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) > 2) c) cc\n" +
            ")\n" +
            "UNION ALL\n" +
            "SELECT * FROM (\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT id FROM customer_registration WHERE is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3))\n" +
            "GROUP BY custom_id HAVING COUNT(custom_id) = 1 ) a) aa,\n" +
            "(SELECT COUNT(1) efS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT id FROM customer_registration WHERE is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3))\n" +
            "GROUP BY custom_id HAVING COUNT(custom_id) = 2 ) b) bb,\n" +
            "(SELECT COUNT(1) dfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT id FROM customer_registration WHERE is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3))\n" +
            "GROUP BY custom_id HAVING COUNT(custom_id) > 2 ) cc) cc\n" +
            ")\n" +
            ") z",nativeQuery = true)
    List getComeNumWRYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT * FROM(\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT id FROM customer_registration WHERE  reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "GROUP BY custom_id HAVING COUNT(custom_id) = 1 ) a) aa,\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT id FROM customer_registration WHERE  reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "GROUP BY custom_id HAVING COUNT(custom_id) = 2 ) b) bb,\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT id FROM customer_registration WHERE  reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "GROUP BY custom_id HAVING COUNT(custom_id) > 2 ) c) cc\n" +
            ")",nativeQuery = true)
    List getComeNumAll(String sTime,String eTime,List userId);

    @Query(value = "SELECT * FROM(\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT id FROM customer_registration WHERE is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "GROUP BY custom_id HAVING COUNT(custom_id) = 1 ) a) aa,\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT id FROM customer_registration WHERE is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "GROUP BY custom_id HAVING COUNT(custom_id) = 2 ) b) bb,\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT id FROM customer_registration WHERE is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "GROUP BY custom_id HAVING COUNT(custom_id) > 2 ) c) cc\n" +
            ")",nativeQuery = true)
    List getComeNumAllZ(String sTime,String eTime);

    @Query(value = "SELECT * FROM (\n" +
            "(SELECT COUNT(1) yfS FROM(SELECT custom_id FROM customer_record WHERE info_time >= ?1 AND info_time <= ?2 AND custom_id IN (SELECT id FROM customer_registration WHERE reception_id in (?3))  GROUP BY\n" +
            "custom_id HAVING COUNT(custom_id) = 1) a) aa,\n" +
            "(SELECT COUNT(1) efS FROM (SELECT custom_id FROM customer_record WHERE info_time >= ?1 AND info_time <= ?2 AND custom_id IN (SELECT id FROM customer_registration WHERE reception_id in (?3))\n" +
            "GROUP BY custom_id HAVING COUNT(custom_id) >1 AND COUNT(custom_id) <= 2) e) ee,\n" +
            "(SELECT COUNT(1) dfS FROM (SELECT custom_id FROM customer_record WHERE info_time >= ?1 AND info_time <= ?2 AND custom_id IN (SELECT id FROM customer_registration WHERE reception_id in (?3))  GROUP BY\n" +
            "custom_id HAVING COUNT(custom_id) >= 3) s) ss\n" +
            ")",nativeQuery = true)
    List getComeNumE(String sTime,String eTime,List userId);

    @Query(value = "SELECT * FROM(\n" +
            "(SELECT COUNT(1) yfS FROM(SELECT custom_id FROM customer_record WHERE custom_id IN (\n" +
            "SELECT id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2  \n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 1) a) aa,\n" +
            "(SELECT COUNT(1) efs FROM(SELECT custom_id FROM customer_record WHERE custom_id IN (\n" +
            "SELECT id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2  \n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 2) b) bb,\n" +
            "(SELECT COUNT(1) dfs FROM(SELECT custom_id FROM customer_record WHERE custom_id IN (\n" +
            "SELECT id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2  \n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) > 2) c) cc\n" +
            ")",nativeQuery = true)
    List getComeNumZ(String sTime,String eTime);

    @Query(value = "SELECT * FROM(\n" +
            "(SELECT COUNT(1) yfS FROM(SELECT custom_id FROM customer_record WHERE custom_id IN (\n" +
            "SELECT id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3) \n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 1) a) aa,\n" +
            "(SELECT COUNT(1) efs FROM(SELECT custom_id FROM customer_record WHERE custom_id IN (\n" +
            "SELECT id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3) \n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 2) b) bb,\n" +
            "(SELECT COUNT(1) dfs FROM(SELECT custom_id FROM customer_record WHERE custom_id IN (\n" +
            "SELECT id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3) \n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) > 2) c) cc\n" +
            ")",nativeQuery = true)
    List getComeNumWRY(String sTime,String eTime,List types);

    @Query(value = "SELECT * FROM (\n" +
            "(SELECT COUNT(1) yfS FROM(SELECT custom_id FROM customer_record WHERE info_time >= ?1 AND info_time <= ?2 AND custom_id IN (SELECT id FROM customer_registration WHERE `level` IN (?4) AND reception_id in (?3))  GROUP BY\n" +
            "custom_id HAVING COUNT(custom_id) = 1) a) aa,\n" +
            "(SELECT COUNT(1) efS FROM (SELECT custom_id FROM customer_record WHERE info_time >= ?1 AND info_time <= ?2 AND custom_id IN (SELECT id FROM customer_registration WHERE `level` IN (?4) AND reception_id in (?3))\n" +
            "GROUP BY custom_id HAVING COUNT(custom_id) >1 AND COUNT(custom_id) <= 2) e) ee,\n" +
            "(SELECT COUNT(1) dfS FROM (SELECT custom_id FROM customer_record WHERE info_time >= ?1 AND info_time <= ?2 AND custom_id IN (SELECT id FROM customer_registration WHERE `level` IN (?4) AND reception_id in (?3))  GROUP BY\n" +
            "custom_id HAVING COUNT(custom_id) >= 3) s) ss\n" +
            ")",nativeQuery = true)
    List getComeNumT(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT * FROM (\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 1) a) aa,\n" +
            "(SELECT COUNT(1) efS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 2) b) bb,\n" +
            "(SELECT COUNT(1) dfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) > 2) c) cc)",nativeQuery = true)
    List getComeNumRW(String sTime,String eTime,List ids);

    @Query(value = "SELECT * FROM (\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?3)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 1) a) aa,\n" +
            "(SELECT COUNT(1) efS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?3)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 2) b) bb,\n" +
            "(SELECT COUNT(1) dfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?3)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) > 2) c) cc)",nativeQuery = true)
    List getComeNumWR(String sTime,String eTime,List types);

    @Query(value = "SELECT * FROM (\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 1) a) aa,\n" +
            "(SELECT COUNT(1) efS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 2) b) bb,\n" +
            "(SELECT COUNT(1) dfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) > 2) c) cc)",nativeQuery = true)
    List getComeNumRWAll(String sTime,String eTime,List ids,List types);

    @Query(value = "SELECT * FROM (\n" +
            "(SELECT COUNT(1) yfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 1) a) aa,\n" +
            "(SELECT COUNT(1) efS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) = 2) b) bb,\n" +
            "(SELECT COUNT(1) dfS FROM(\n" +
            "SELECT custom_id FROM customer_record WHERE custom_id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY custom_id HAVING COUNT(custom_id) > 2) c) cc)",nativeQuery = true)
    List getComeNumCAll(String sTime,String eTime);

    @Query(value = "SELECT access_knowledge,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ") GROUP BY access_knowledge",nativeQuery = true)
    List getAccessKnowledge(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT access_knowledge,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY access_knowledge",nativeQuery = true)
    List getAccessKnowledge(String sTime,String eTime,List userId);

    @Query(value = "SELECT access_knowledge,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4) GROUP BY access_knowledge",nativeQuery = true)
    List getAccessKnowledgeW(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT access_knowledge,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 GROUP BY access_knowledge",nativeQuery = true)
    List getAccessKnowledgeW(String sTime,String eTime,List userId);

    @Query(value = "SELECT a.access_knowledge,SUM(a.num) FROM(\n" +
            "SELECT access_knowledge,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?4))\n" +
            "GROUP BY access_knowledge\n" +
            "UNION ALL\n" +
            "SELECT access_knowledge,COUNT(1) num FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?4) GROUP BY access_knowledge\n" +
            ") a GROUP BY a.access_knowledge",nativeQuery = true)
    List getAccessKnowledgeAll(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT access_knowledge,COUNT(1) FROM customer_registration WHERE reception_id in (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY access_knowledge",nativeQuery = true)
    List getAccessKnowledgeAll(String sTime,String eTime,List userId);

    @Query(value = "SELECT access_knowledge,COUNT(1) FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY access_knowledge",nativeQuery = true)
    List getAccessKnowledgeAllZ(String sTime,String eTime);

    @Query(value = "SELECT access_knowledge,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY access_knowledge",nativeQuery = true)
    List getAccessKnowledgeCAll(String sTime,String eTime);

    @Query(value = "SELECT access_knowledge,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?3)\n" +
            ") GROUP BY access_knowledge",nativeQuery = true)
    List getAccessKnowledgeWR(String sTime,String eTime,List types);

    @Query(value = "SELECT access_knowledge,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY access_knowledge",nativeQuery = true)
    List getAccessKnowledgeRW(String sTime,String eTime,List userId);

    @Query(value = "SELECT access_knowledge,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ") GROUP BY access_knowledge",nativeQuery = true)
    List getAccessKnowledgeRWAll(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT access_knowledge,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 GROUP BY access_knowledge",nativeQuery = true)
    List getAccessKnowledgeZ(String sTime,String eTime);

    @Query(value = "SELECT access_knowledge,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3) GROUP BY access_knowledge",nativeQuery = true)
    List getAccessKnowledgeWRY(String sTime,String eTime,List types);

    @Query(value = "SELECT home_ownership,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ")GROUP BY home_ownership",nativeQuery = true)
    List getHomeOwnership(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT home_ownership,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ")GROUP BY home_ownership",nativeQuery = true)
    List getHomeOwnership(String sTime,String eTime,List userId);

    @Query(value = "SELECT home_ownership,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4)\n" +
            "GROUP BY home_ownership",nativeQuery = true)
    List getHomeOwnershipW(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT home_ownership,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "GROUP BY home_ownership",nativeQuery = true)
    List getHomeOwnershipW(String sTime,String eTime,List userId);

    @Query(value = "SELECT a.home_ownership,SUM(a.num) FROM(\n" +
            "SELECT home_ownership,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?4))\n" +
            "GROUP BY home_ownership\n" +
            "UNION ALL\n" +
            "SELECT home_ownership,COUNT(1) num FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?4) GROUP BY home_ownership\n" +
            ") a GROUP BY a.home_ownership",nativeQuery = true)
    List getHomeOwnershipAll(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT a.home_ownership,SUM(a.num) FROM(\n" +
            "SELECT home_ownership,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?3))\n" +
            "GROUP BY home_ownership\n" +
            "UNION ALL\n" +
            "SELECT home_ownership,COUNT(1) num FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?3) GROUP BY home_ownership\n" +
            ") a GROUP BY a.home_ownership",nativeQuery = true)
    List getHomeOwnershipWRYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT home_ownership,COUNT(1) FROM customer_registration WHERE reception_id in (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY home_ownership",nativeQuery = true)
    List getHomeOwnershipAll(String sTime,String eTime,List userId);

    @Query(value = "SELECT home_ownership,COUNT(1) FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND total_need IS NOT NULL GROUP BY home_ownership",nativeQuery = true)
    List getHomeOwnershipAllZ(String sTime,String eTime);

    @Query(value = "SELECT home_ownership,COUNT(1) num FROM customer_registration \n" +
            "WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND reception_id IN (?3) AND home_ownership IS NOT NULL\n" +
            "GROUP BY home_ownership",nativeQuery = true)
    List getHomeOwnershipE(String sTime,String eTime,List userId);

    @Query(value = "SELECT home_ownership,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "GROUP BY home_ownership",nativeQuery = true)
    List getHomeOwnershipZ(String sTime,String eTime);

    @Query(value = "SELECT home_ownership,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3)\n" +
            "GROUP BY home_ownership",nativeQuery = true)
    List getHomeOwnershipWRY(String sTime,String eTime,List types);

    @Query(value = "SELECT home_ownership,COUNT(1) num FROM customer_registration WHERE id IN\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ")GROUP BY home_ownership",nativeQuery = true)
    List getHomeOwnershipRW(String sTime,String eTime,List userId);

    @Query(value = "SELECT home_ownership,COUNT(1) num FROM customer_registration WHERE id IN\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ")GROUP BY home_ownership",nativeQuery = true)
    List getHomeOwnershipWR(String sTime,String eTime,List types);

    @Query(value = "SELECT home_ownership,COUNT(1) num FROM customer_registration WHERE id IN\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ")GROUP BY home_ownership",nativeQuery = true)
    List getHomeOwnershipCAll(String sTime,String eTime);

    @Query(value = "SELECT payment_type,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ")GROUP BY payment_type",nativeQuery = true)
    List getPaymentType(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT payment_type,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ")GROUP BY payment_type",nativeQuery = true)
    List getPaymentType(String sTime,String eTime,List userId);

    @Query(value = "SELECT payment_type,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4)\n" +
            "GROUP BY payment_type",nativeQuery = true)
    List getPaymentTypeW(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT payment_type,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "GROUP BY payment_type",nativeQuery = true)
    List getPaymentTypeW(String sTime,String eTime,List userId);

    @Query(value = "SELECT payment_type,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ")GROUP BY payment_type",nativeQuery = true)
    List getPaymentTypeRW(String sTime,String eTime,List ids);

    @Query(value = "SELECT payment_type,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?3)\n" +
            ")GROUP BY payment_type",nativeQuery = true)
    List getPaymentTypeWR(String sTime,String eTime,List types);

    @Query(value = "SELECT payment_type,COUNT(1) FROM customer_registration\n" +
            "WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND reception_id IN (?3) GROUP BY payment_type",nativeQuery = true)
    List getPaymentTypeE(String sTime,String eTime,List userId);

    @Query(value = "SELECT payment_type,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "GROUP BY payment_type",nativeQuery = true)
    List getPaymentTypeZ(String sTime,String eTime);

    @Query(value = "SELECT payment_type,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3)\n" +
            "GROUP BY payment_type",nativeQuery = true)
    List getPaymentTypeT(String sTime,String eTime,List types);

    @Query(value = "SELECT payment_type,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ")GROUP BY payment_type",nativeQuery = true)
    List getPaymentTypeCAll(String sTime,String eTime);

    @Query(value = "SELECT consider_type,COUNT(1) FROM customer_registration\n" +
            "WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND reception_id = ?3 AND `level` in (?4) \n" +
            "GROUP BY consider_type",nativeQuery = true)
    List getConsiderType(String sTime,String eTime,String userId,List types);

    @Query(value = "SELECT consider_type,COUNT(1) FROM customer_registration\n" +
            "WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND reception_id IN (?3)\n" +
            "GROUP BY consider_type",nativeQuery = true)
    List getConsiderTypeE(String sTime,String eTime,List userId);

    @Query(value = "SELECT consider_type,COUNT(1) FROM customer_registration\n" +
            "WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2\n" +
            "GROUP BY consider_type",nativeQuery = true)
    List getConsiderTypeZ(String sTime,String eTime);

    @Query(value = "SELECT consider_type,COUNT(1) FROM customer_registration\n" +
            "WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND reception_id IN (?3) AND `level` in (?4) \n" +
            "GROUP BY consider_type",nativeQuery = true)
    List getConsiderTypeT(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT consider_type,COUNT(1) FROM customer_registration\n" +
            "WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND level in (?3) \n" +
            "GROUP BY consider_type",nativeQuery = true)
    List getConsiderTypeN(String sTime,String eTime,List types);

    @Query(value = "SELECT family_structure,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ") GROUP BY family_structure",nativeQuery = true)
    List getFamilyStructure(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT family_structure,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY family_structure",nativeQuery = true)
    List getFamilyStructure(String sTime,String eTime,List userId);

    @Query(value = "SELECT family_structure,COUNT(1) FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4)\n" +
            "GROUP BY family_structure",nativeQuery = true)
    List getFamilyStructureW(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT family_structure,COUNT(1) FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "GROUP BY family_structure",nativeQuery = true)
    List getFamilyStructureW(String sTime,String eTime,List userId);

    @Query(value = "SELECT a.family_structure,SUM(a.num) FROM(\n" +
            "SELECT family_structure,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?4))\n" +
            "GROUP BY family_structure\n" +
            "UNION ALL\n" +
            "SELECT family_structure,COUNT(1) num FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?4) GROUP BY family_structure\n" +
            ") a GROUP BY a.family_structure",nativeQuery = true)
    List getFamilyStructureAll(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT a.family_structure,SUM(a.num) FROM(\n" +
            "SELECT family_structure,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?3))\n" +
            "GROUP BY family_structure\n" +
            "UNION ALL\n" +
            "SELECT family_structure,COUNT(1) num FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?3) GROUP BY family_structure\n" +
            ") a GROUP BY a.family_structure",nativeQuery = true)
    List getFamilyStructureWRYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT family_structure,COUNT(1) FROM customer_registration WHERE reception_id in (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY family_structure",nativeQuery = true)
    List getFamilyStructureAll(String sTime,String eTime,List userId);

    @Query(value = "SELECT family_structure,COUNT(1) FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY family_structure",nativeQuery = true)
    List getFamilyStructureAllZ(String sTime,String eTime);

    @Query(value = "SELECT family_structure,COUNT(1) FROM customer_registration\n" +
            "WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND reception_id IN (?3)\n" +
            "GROUP BY family_structure",nativeQuery = true)
    List getFamilyStructureE(String sTime,String eTime,List userId);

    @Query(value = "SELECT family_structure,COUNT(1) FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "GROUP BY family_structure",nativeQuery = true)
    List getFamilyStructureZ(String sTime,String eTime);

    @Query(value = "SELECT family_structure,COUNT(1) FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3)\n" +
            "GROUP BY family_structure",nativeQuery = true)
    List getFamilyStructureWRY(String sTime,String eTime,List types);

    @Query(value = "SELECT family_structure,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY family_structure",nativeQuery = true)
    List getFamilyStructureRW(String sTime,String eTime,List userId);

    @Query(value = "SELECT family_structure,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?3)\n" +
            ") GROUP BY family_structure",nativeQuery = true)
    List getFamilyStructureWR(String sTime,String eTime,List types);

    @Query(value = "SELECT family_structure,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY family_structure",nativeQuery = true)
    List getFamilyStructureCAll(String sTime,String eTime);

    @Query(value = "SELECT customer_occupation,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ") GROUP BY customer_occupation",nativeQuery = true)
    List getCustomerOccupation(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT customer_occupation,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY customer_occupation",nativeQuery = true)
    List getCustomerOccupation(String sTime,String eTime,List userId);

    @Query(value = "SELECT customer_occupation,COUNT(1) FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4)\n" +
            "GROUP BY customer_occupation",nativeQuery = true)
    List getCustomerOccupationW(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT customer_occupation,COUNT(1) FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 \n" +
            "GROUP BY customer_occupation",nativeQuery = true)
    List getCustomerOccupationW(String sTime,String eTime,List userId);

    @Query(value = "SELECT a.customer_occupation,SUM(a.num) FROM(\n" +
            "SELECT customer_occupation,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?4))\n" +
            "GROUP BY customer_occupation\n" +
            "UNION ALL\n" +
            "SELECT customer_occupation,COUNT(1) num FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?4) GROUP BY customer_occupation\n" +
            ") a GROUP BY a.customer_occupation",nativeQuery = true)
    List getCustomerOccupationAll(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT a.customer_occupation,SUM(a.num) FROM(\n" +
            "SELECT customer_occupation,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?3))\n" +
            "GROUP BY customer_occupation\n" +
            "UNION ALL\n" +
            "SELECT customer_occupation,COUNT(1) num FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?3) GROUP BY customer_occupation\n" +
            ") a GROUP BY a.customer_occupation",nativeQuery = true)
    List getCustomerOccupationWRYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT customer_occupation,COUNT(1) FROM customer_registration WHERE reception_id in (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY customer_occupation",nativeQuery = true)
    List getCustomerOccupationAll(String sTime,String eTime,List userId);

    @Query(value = "SELECT customer_occupation,COUNT(1) FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY customer_occupation",nativeQuery = true)
    List getCustomerOccupationAllZ(String sTime,String eTime);

    @Query(value = "SELECT customer_occupation,COUNT(1) FROM customer_registration\n" +
            "WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND reception_id IN (?3)\n" +
            "GROUP BY customer_occupation",nativeQuery = true)
    List getCustomerOccupationE(String sTime,String eTime,List userId);

    @Query(value = "SELECT customer_occupation,COUNT(1) FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 \n" +
            "GROUP BY customer_occupation",nativeQuery = true)
    List getCustomerOccupationZ(String sTime,String eTime);

    @Query(value = "SELECT customer_occupation,COUNT(1) FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4)\n" +
            "GROUP BY customer_occupation",nativeQuery = true)
    List getCustomerOccupationN(String sTime,String eTime,List types);

    @Query(value = "SELECT customer_occupation,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY customer_occupation",nativeQuery = true)
    List getCustomerOccupationRW(String sTime,String eTime,List userId);

    @Query(value = "SELECT customer_occupation,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?3)\n" +
            ") GROUP BY customer_occupation",nativeQuery = true)
    List getCustomerOccupationWR(String sTime,String eTime,List types);

    @Query(value = "SELECT customer_occupation,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY customer_occupation",nativeQuery = true)
    List getCustomerOccupationCAll(String sTime,String eTime);

    @Query(value = "SELECT apartment_structure,COUNT(1)  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4) GROUP BY apartment_structure",nativeQuery = true)
    List getTypeOfClientele(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT apartment_structure,COUNT(1)  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            " GROUP BY apartment_structure",nativeQuery = true)
    List getTypeOfClientele(String sTime,String eTime,List userId);

    @Query(value = "SELECT apartment_structure,COUNT(1)  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "GROUP BY apartment_structure",nativeQuery = true)
    List getTypeOfClienteleRW(String sTime,String eTime,List userId);

    @Query(value = "SELECT apartment_structure,COUNT(1)  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?3) GROUP BY apartment_structure",nativeQuery = true)
    List getTypeOfClienteleWR(String sTime,String eTime,List types);

    @Query(value = "SELECT apartment_structure,COUNT(1)  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "GROUP BY apartment_structure",nativeQuery = true)
    List getTypeOfClienteleCAll(String sTime,String eTime);

    @Query(value = "SELECT discount_total,COUNT(1) num FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4) GROUP BY discount_total",nativeQuery = true)
    List getTotalClientPrice(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT discount_total,COUNT(1) num FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            " GROUP BY discount_total",nativeQuery = true)
    List getTotalClientPrice(String sTime,String eTime,List userId);

    @Query(value = "SELECT discount_total,COUNT(1) num FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "GROUP BY discount_total",nativeQuery = true)
    List getTotalClientPriceRW(String sTime,String eTime,List userId);

    @Query(value = "SELECT discount_total,COUNT(1) num FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?3) GROUP BY discount_total",nativeQuery = true)
    List getTotalClientPriceWR(String sTime,String eTime,List types);

    @Query(value = "SELECT discount_total,COUNT(1) num FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "GROUP BY discount_total",nativeQuery = true)
    List getTotalClientPriceCAll(String sTime,String eTime);


    @Query(value = "SELECT check_out_reason,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND property_tye IN (?4))\n" +
            ") GROUP BY check_out_reason",nativeQuery = true)
    List getCheckOutReason(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT check_out_reason,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            ") GROUP BY check_out_reason",nativeQuery = true)
    List getCheckOutReason(String sTime,String eTime,List userId);

    @Query(value = "SELECT check_out_reason,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY check_out_reason",nativeQuery = true)
    List getCheckOutReasonRW(String sTime,String eTime,List userId);

    @Query(value = "SELECT check_out_reason,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND property_tye IN (?3))\n" +
            ") GROUP BY check_out_reason",nativeQuery = true)
    List getCheckOutReasonWR(String sTime,String eTime,List types);

    @Query(value = "SELECT check_out_reason,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY check_out_reason",nativeQuery = true)
    List getCheckOutReasonCAll(String sTime,String eTime);

    @Query(value = "SELECT face,COUNT(1) FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND property_tye IN (?4)) GROUP BY face",nativeQuery = true)
    List getPromotingProducts(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT face,COUNT(1) FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            " GROUP BY face",nativeQuery = true)
    List getPromotingProducts(String sTime,String eTime,List userId);

    @Query(value = "SELECT face,COUNT(1) FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2) GROUP BY face",nativeQuery = true)
    List getPromotingProductsRW(String sTime,String eTime,List userId);

    @Query(value = "SELECT face,COUNT(1) FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND property_tye IN (?4)) GROUP BY face",nativeQuery = true)
    List getPromotingProductsWR(String sTime,String eTime,List types);

    @Query(value = "SELECT face,COUNT(1) FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2) GROUP BY face",nativeQuery = true)
    List getPromotingProductsCAll(String sTime,String eTime);

    @Query(value = "SELECT face,COUNT(1) num FROM house_detail WHERE related_party IN (\n" +
            "SELECT id FROM customer_registration WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND reception_id = ?3 AND level in (?4))\n" +
            "GROUP BY face",nativeQuery = true)
    List getSalesStatistics(String sTime,String eTime,String userId,List types);

    @Query(value = "SELECT face,COUNT(1) num FROM house_detail WHERE related_party IN (\n" +
            "SELECT id FROM customer_registration WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND reception_id IN (?3) AND level in (?4))\n" +
            "GROUP BY face",nativeQuery = true)
    List getSalesStatisticsT(String sTime,String eTime,List userId,List types);


    @Query(value = "SELECT face,COUNT(1) num FROM house_detail WHERE related_party IN (\n" +
            "SELECT id FROM customer_registration WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND level in (?3))\n" +
            "GROUP BY face",nativeQuery = true)
    List getSalesStatisticsN(String sTime,String eTime,List types);


    @Query(value = "SELECT * FROM(\n" +
            "(SELECT COUNT(1) lf FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND visit = 1 AND reception_id = ?3 AND `level`in (?4)) a,\n" +
            "(SELECT COUNT(1) cj FROM customer_registration WHERE effective = 1 AND come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND reception_id = ?3 AND `level` in (?4) ) b)",nativeQuery = true)
    List getSalesConversionRate(String sTime,String eTime,String userId,List types);

    @Query(value = "SELECT * FROM(\n" +
            "(SELECT COUNT(1) lf FROM customer_registration WHERE id IN (\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND property_tye IN (?4)) \n" +
            ") AND visit = 1) a,\n" +
            "(SELECT COUNT(1) cj FROM customer_registration WHERE id IN (\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND property_tye IN (?4)) \n" +
            ")) b\n" +
            ")",nativeQuery = true)
    List getSalesConversionRateT(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT * FROM(\n" +
            "(SELECT COUNT(1) lf FROM customer_registration WHERE id IN (\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            ") AND visit = 1) a,\n" +
            "(SELECT COUNT(1) cj FROM customer_registration WHERE id IN (\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "))) b\n" +
            ")",nativeQuery = true)
    List getSalesConversionRateT(String sTime,String eTime,List userId);

    @Query(value = "SELECT * FROM(\n" +
            "(SELECT COUNT(1) lf FROM customer_registration WHERE id IN (\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2) \n" +
            ") AND visit = 1) a,\n" +
            "(SELECT COUNT(1) cj FROM customer_registration WHERE id IN (\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2) \n" +
            ")) b\n" +
            ")",nativeQuery = true)
    List getSalesConversionRateCAll(String sTime,String eTime);

    @Query(value = "SELECT * FROM(\n" +
            "(SELECT COUNT(1) lf FROM customer_registration WHERE id IN (\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND property_tye IN (?3)) \n" +
            ") AND visit = 1) a,\n" +
            "(SELECT COUNT(1) cj FROM customer_registration WHERE id IN (\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND property_tye IN (?3)) \n" +
            ")) b\n" +
            ")",nativeQuery = true)
    List getSalesConversionRateN(String sTime,String eTime,List types);

    @Query(value = "SELECT payment_type,COUNT(1)  num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2  AND is_system = 0 AND effective = 1 AND reception_id = ?3 AND `level` in (?4)\n" +
            "GROUP BY payment_type",nativeQuery = true)
    List getCustomerSalesStatus(String sTime,String eTime,String userId,List types);

    @Query(value = "SELECT payment_type,COUNT(1)  num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2  AND is_system = 0 AND effective = 1 AND reception_id IN (?3) AND `level` in (?4)\n" +
            "GROUP BY payment_type",nativeQuery = true)
    List getCustomerSalesStatusT(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT payment_type,COUNT(1)  num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2  AND is_system = 0 AND effective = 1 AND `level` in (?3)\n" +
            "GROUP BY payment_type",nativeQuery = true)
    List getCustomerSalesStatusN(String sTime,String eTime,List types);


    @Query(value = "SELECT b.rc,FORMAT(b.rc/a.zs*100,0) rcbfb,c.dj,FORMAT(c.dj/a.zs*100,0) djbfb,d.sf,FORMAT(d.sf/a.zs*100,0) sfbfb,e.qk,FORMAT(e.qk/a.zs*100,0) qkbfb FROM (\n" +
            "(SELECT COUNT(1) zs FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND effective = 1 AND reception_id = ?3 AND `level` in (?4)) a,\n" +
            "(SELECT COUNT(1) rc FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND effective = 1 identify = 1 AND reception_id = ?3 AND `level` in (?4)) b,\n" +
            "(SELECT COUNT(1) dj FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND effective = 1 large_sum = 1 AND reception_id = ?3 AND `level` in (?4)) c,\n" +
            "(SELECT COUNT(1) sf FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND effective = 1 down_payments = 1 AND reception_id = ?3 AND `level` in (?4)) d,\n" +
            "(SELECT COUNT(1) qk FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND effective = 1 payment_type LIKE '%全%' AND reception_id = ?3 AND `level` in (?4)) e\n" +
            ")",nativeQuery = true)
    List getAmong(String sTime,String eTime,String userId,List types);

    @Query(value = "SELECT b.rc,FORMAT(b.rc/a.zs*100,0) rcbfb,c.dj,FORMAT(c.dj/a.zs*100,0) djbfb,d.sf,FORMAT(d.sf/a.zs*100,0) sfbfb,e.qk,FORMAT(e.qk/a.zs*100,0) qkbfb FROM (\n" +
            "(SELECT COUNT(1) zs FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND effective = 1 AND reception_id IN (?3) AND `level` in (?4)) a,\n" +
            "(SELECT COUNT(1) rc FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND effective = 1 identify = 1 AND reception_id IN (?3) AND `level` in (?4)) b,\n" +
            "(SELECT COUNT(1) dj FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND effective = 1 large_sum = 1 AND reception_id IN (?3) AND `level` in (?4)) c,\n" +
            "(SELECT COUNT(1) sf FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND effective = 1 down_payments = 1 AND reception_id IN (?3) AND `level` in (?4)) d,\n" +
            "(SELECT COUNT(1) qk FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND effective = 1 payment_type LIKE '%全%' AND reception_id IN (?3) AND `level` in (?4)) e\n" +
            ")",nativeQuery = true)
    List getAmongT(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT b.rc,FORMAT(b.rc/a.zs*100,0) rcbfb,c.dj,FORMAT(c.dj/a.zs*100,0) djbfb,d.sf,FORMAT(d.sf/a.zs*100,0) sfbfb,e.qk,FORMAT(e.qk/a.zs*100,0) qkbfb FROM (\n" +
            "(SELECT COUNT(1) zs FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND effective = 1 AND `level` in (?3)) a,\n" +
            "(SELECT COUNT(1) rc FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND effective = 1 identify = 1 AND `level` in (?3)) b,\n" +
            "(SELECT COUNT(1) dj FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND effective = 1 large_sum = 1 AND `level` in (?3)) c,\n" +
            "(SELECT COUNT(1) sf FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND effective = 1 down_payments = 1 AND `level` in (?3)) d,\n" +
            "(SELECT COUNT(1) qk FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND effective = 1 payment_type LIKE '%全%' AND `level` in (?3)) e\n" +
            ")",nativeQuery = true)
    List getAmongN(String sTime,String eTime,List types);


    @Query(value = "SELECT area_need,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND reception_id IN (?3) AND `level` IN (?4) " +
            "GROUP BY area_need",nativeQuery = true)
    List getAreaNeed(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT area_need,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND `level` IN (?3) " +
            "GROUP BY area_need",nativeQuery = true)
    List getAreaNeedT(String sTime,String eTime,List types);

    @Query(value = "SELECT area_need,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND reception_id IN (?3) " +
            "GROUP BY area_need",nativeQuery = true)
    List getAreaNeedE(String sTime,String eTime,List userId);

    @Query(value = "SELECT area_need,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 " +
            "GROUP BY area_need",nativeQuery = true)
    List getAreaNeedZ(String sTime,String eTime);

    @Query(value = "SELECT intentional_household_type,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 and reception_id IN (?3) AND `level` IN (?4)\n" +
            "GROUP BY intentional_household_type",nativeQuery = true)
    List getIntentionalHouseholdType(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT intentional_household_type,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND `level` IN (?3)\n" +
            "GROUP BY intentional_household_type",nativeQuery = true)
    List getIntentionalHouseholdTypeT(String sTime,String eTime,List types);

    @Query(value = "SELECT intentional_household_type,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 and reception_id IN (?3)\n" +
            "GROUP BY intentional_household_type",nativeQuery = true)
    List getIntentionalHouseholdTypeE(String sTime,String eTime,List userId);

    @Query(value = "SELECT intentional_household_type,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0\n" +
            "GROUP BY intentional_household_type",nativeQuery = true)
    List getIntentionalHouseholdTypeZ(String sTime,String eTime);

    @Query(value = "SELECT age,id FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 and reception_id IN (?3) AND `level` IN (?4) AND age IS NOT NULL",nativeQuery = true)
    List<Object> getAge(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT age,id  FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4) AND age IS NOT NULL",nativeQuery = true)
    List<Object> getAgeW(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT age,id  FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND age IS NOT NULL",nativeQuery = true)
    List<Object> getAgeW(String sTime,String eTime,List userId);

    @Query(value = "SELECT age,id FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND age IS NOT NULL)\n" +
            "AND property_tye IN (?4))\n" +
            "UNION ALL\n" +
            "SELECT age,id FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND age IS NOT NULL\n" +
            "AND intention_property_type IN (?4)",nativeQuery = true)
    List<Object> getAgeAll(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT age,id FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND age IS NOT NULL)\n" +
            "AND property_tye IN (?3))\n" +
            "UNION ALL\n" +
            "SELECT age,id FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND age IS NOT NULL\n" +
            "AND intention_property_type IN (?3)",nativeQuery = true)
    List<Object> getAgeWRYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT age,id FROM customer_registration WHERE reception_id in (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND age IS NOT NULL",nativeQuery = true)
    List<Object> getAgeAll(String sTime,String eTime,List userId);

    @Query(value = "SELECT age,id FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0",nativeQuery = true)
    List<Object> getAgeAllZ(String sTime,String eTime);

    @Query(value = "SELECT age,id  FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3)",nativeQuery = true)
    List<Object> getAgeT(String sTime,String eTime,List types);

    @Query(value = "SELECT age,id FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 and reception_id IN (?3) AND age IS NOT NULL",nativeQuery = true)
    List<Object> getAgeE(String sTime,String eTime,List userId);

    @Query(value = "SELECT age,id  FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2",nativeQuery = true)
    List<Object> getAgeZ(String sTime,String eTime);

    @Query(value = "SELECT total_need,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 and reception_id IN (?3) AND `level` IN (?4)\n" +
            "GROUP BY total_need",nativeQuery = true)
    List getTotalNeed(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT total_need,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND `level` IN (?3)\n" +
            "GROUP BY total_need",nativeQuery = true)
    List getTotalNeedT(String sTime,String eTime,List types);

    @Query(value = "SELECT total_need,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 and reception_id IN (?3)\n" +
            "GROUP BY total_need",nativeQuery = true)
    List getTotalNeedE(String sTime,String eTime,List userId);

    @Query(value = "SELECT total_need,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0\n" +
            "GROUP BY total_need",nativeQuery = true)
    List getTotalNeedZ(String sTime,String eTime);

    @Query(value = "SELECT price_acceptance,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 and reception_id IN (?3) AND `level` IN (?4)\n" +
            "GROUP BY price_acceptance",nativeQuery = true)
    List getPriceAcceptance(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT price_acceptance,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4)\n" +
            "GROUP BY price_acceptance",nativeQuery = true)
    List getPriceAcceptanceW(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT price_acceptance,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "GROUP BY price_acceptance",nativeQuery = true)
    List getPriceAcceptanceW(String sTime,String eTime,List userId);

    @Query(value = "SELECT price_acceptance,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "GROUP BY price_acceptance",nativeQuery = true)
    List getPriceAcceptanceWZ(String sTime,String eTime);

    @Query(value = "SELECT a.price_acceptance,SUM(a.num) FROM(\n" +
            "SELECT price_acceptance,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?4))\n" +
            "GROUP BY price_acceptance\n" +
            "UNION ALL\n" +
            "SELECT price_acceptance,COUNT(1) num FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?4) GROUP BY price_acceptance\n" +
            ") a GROUP BY a.price_acceptance",nativeQuery = true)
    List getPriceAcceptanceAll(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT a.price_acceptance,SUM(a.num) FROM(\n" +
            "SELECT price_acceptance,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?3))\n" +
            "GROUP BY price_acceptance\n" +
            "UNION ALL\n" +
            "SELECT price_acceptance,COUNT(1) num FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?3) GROUP BY price_acceptance\n" +
            ") a GROUP BY a.price_acceptance",nativeQuery = true)
    List getPriceAcceptanceWRYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT price_acceptance,COUNT(1) FROM customer_registration WHERE reception_id in (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY price_acceptance",nativeQuery = true)
    List getPriceAcceptanceAll(String sTime,String eTime,List userId);

    @Query(value = "SELECT price_acceptance,COUNT(1) FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY price_acceptance",nativeQuery = true)
    List getPriceAcceptanceAllZ(String sTime,String eTime);

    @Query(value = "SELECT price_acceptance,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3)\n" +
            "GROUP BY price_acceptance",nativeQuery = true)
    List getPriceAcceptanceT(String sTime,String eTime,List types);


    @Query(value = "SELECT price_acceptance,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 and reception_id IN (?3)\n" +
            "GROUP BY price_acceptance",nativeQuery = true)
    List getPriceAcceptanceE(String sTime,String eTime,List userId);

    @Query(value = "SELECT price_acceptance,COUNT(1) num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "GROUP BY price_acceptance",nativeQuery = true)
    List getPriceAcceptanceZ(String sTime,String eTime);

    @Query(value = "SELECT house_type_need,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 and reception_id IN (?3) AND `level` IN (?4)\n" +
            "GROUP BY house_type_need",nativeQuery = true)
    List getHouseTypeNeed(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT house_type_need,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND `level` IN (?3)\n" +
            "GROUP BY house_type_need",nativeQuery = true)
    List getHouseTypeNeedT(String sTime,String eTime,List types);


    @Query(value = "SELECT house_type_need,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 and reception_id IN (?3)\n" +
            "GROUP BY house_type_need",nativeQuery = true)
    List getHouseTypeNeedE(String sTime,String eTime,List userId);

    @Query(value = "SELECT house_type_need,COUNT(1) num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0\n" +
            "GROUP BY house_type_need",nativeQuery = true)
    List getHouseTypeNeedZ(String sTime,String eTime);

    @Query(value = "SELECT id,`name`,cover,spot FROM `user` WHERE `level` = 1 AND `status` = 1",nativeQuery = true)
    List<Object> getUserList();

    @Query(value = "SELECT d.`name`,d.id userId,d.cover,d.spot,a.xszt,e.rownum,b.zsze,f.zerownum,c.sf,b.zsze - c.sf ye FROM " +
            "(SELECT COUNT(1) xszt FROM customer_registration WHERE reception_id = ?1 AND effective = 1 AND come_time BETWEEN ?2 AND ?3 AND `level` IN (?4)) a, " +
            "(SELECT SUM(discount_total) zsze FROM house_detail WHERE related_party IN " +
            "(SELECT id FROM customer_registration WHERE reception_id = ?1 AND come_time BETWEEN ?2 AND ?3 AND `level` IN (?4))) b, " +
            "(SELECT SUM(payment_type_money) sf FROM customer_registration WHERE reception_id = ?1 AND effective = 1 AND come_time BETWEEN ?2 AND ?3 AND `level` IN (?4)) c, " +
            "(SELECT name,id,cover,spot FROM `user` WHERE id = ?1) d, " +
            "(SELECT z.rownum FROM (SELECT t.*,@rownum \\:= @rownum + 1 AS rownum FROM (SELECT @rownum \\:= 0 ) r, " +
            "(SELECT reception_id,COUNT(1) zs FROM customer_registration WHERE reception_id IN (SELECT id FROM `user` WHERE `level` = 1 AND `status` = 1) AND effective = 1 AND come_time BETWEEN ?2 AND ?3 AND `level` IN (?4) " +
            "GROUP BY reception_id ORDER BY zs DESC) AS t ) z WHERE z.reception_id = ?1) e, " +
            "(SELECT n.zerownum FROM (SELECT p.*,@rank  \\:= @rank + 1 AS zerownum FROM (SELECT @rank \\:= 0 ) m, " +
            "(SELECT related_adviser,SUM(discount_total) q FROM house_detail WHERE related_adviser IN " +
            "(SELECT reception_id FROM customer_registration WHERE come_time BETWEEN ?2 AND ?3 AND is_system = 0 AND effective = 1 AND `level` IN (?4)) AND type = 4 " +
            "GROUP BY related_adviser ORDER BY q DESC) AS p ) n WHERE n.related_adviser = ?1) f",nativeQuery = true)
    List getCjCustomer(String cid,String sTime,String eTime,List type);

    @Query(value = "select * from customer_registration where level = ?1 AND is_system = 0 AND customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房')",nativeQuery = true)
    List<CustomerRegistration> findByLevel(String level);

    @Query(value = "select sale_type from customer_registration where id = ?1",nativeQuery = true)
    int findBySaleType(Integer id);


    @Modifying
    @Transactional
    @Query("update CustomerRegistration a set " +
            "a.name = CASE WHEN :#{#customerRegistration.name} IS NULL THEN a.name ELSE :#{#customerRegistration.name} END ," +
            "a.comeNum = CASE WHEN :#{#customerRegistration.comeNum} IS NULL THEN a.comeNum ELSE :#{#customerRegistration.comeNum} END ," +
            "a.address = CASE WHEN :#{#customerRegistration.address} IS NULL THEN a.address ELSE :#{#customerRegistration.address} END ," +
            "a.phone = CASE WHEN :#{#customerRegistration.phone} IS NULL THEN a.phone ELSE :#{#customerRegistration.phone} END ," +
            "a.virtualNumber = CASE WHEN :#{#customerRegistration.virtualNumber} IS NULL THEN a.virtualNumber ELSE :#{#customerRegistration.virtualNumber} END ," +
            "a.homeOwnership = CASE WHEN :#{#customerRegistration.homeOwnership} IS NULL THEN a.homeOwnership ELSE :#{#customerRegistration.homeOwnership} END ," +
            "a.intentionalHouseholdType = CASE WHEN :#{#customerRegistration.intentionalHouseholdType} IS NULL THEN a.intentionalHouseholdType ELSE :#{#customerRegistration.intentionalHouseholdType} END ," +
            "a.accessKnowledge = CASE WHEN :#{#customerRegistration.accessKnowledge} IS NULL THEN a.accessKnowledge ELSE :#{#customerRegistration.accessKnowledge} END ," +
            "a.customerIntention = CASE WHEN :#{#customerRegistration.customerIntention} IS NULL THEN a.customerIntention ELSE :#{#customerRegistration.customerIntention} END ," +
            "a.comeTime = CASE WHEN :#{#customerRegistration.comeTime} IS NULL THEN a.comeTime ELSE :#{#customerRegistration.comeTime} END ," +
            "a.customerSource = CASE WHEN :#{#customerRegistration.customerSource} IS NULL THEN a.customerSource ELSE :#{#customerRegistration.customerSource} END ," +
            "a.level = CASE WHEN :#{#customerRegistration.level} IS NULL THEN a.level ELSE :#{#customerRegistration.level} END ," +
            "a.sex = CASE WHEN :#{#customerRegistration.sex} IS NULL THEN a.sex ELSE :#{#customerRegistration.sex} END ," +
            "a.identify = CASE WHEN :#{#customerRegistration.identify} IS NULL THEN a.identify ELSE :#{#customerRegistration.identify} END ," +
            "a.identifyMoney = CASE WHEN :#{#customerRegistration.identifyMoney} IS NULL THEN a.identifyMoney ELSE :#{#customerRegistration.identifyMoney} END ," +
            "a.largeSum = CASE WHEN :#{#customerRegistration.largeSum} IS NULL THEN a.largeSum ELSE :#{#customerRegistration.largeSum} END ," +
            "a.largeSumMoney = CASE WHEN :#{#customerRegistration.largeSumMoney} IS NULL THEN a.largeSumMoney ELSE :#{#customerRegistration.largeSumMoney} END , " +
            "a.smallSum = CASE WHEN :#{#customerRegistration.smallSum} IS NULL THEN a.smallSum ELSE :#{#customerRegistration.smallSum} END ," +
            "a.smallSumMoney = CASE WHEN :#{#customerRegistration.smallSumMoney} IS NULL THEN a.smallSumMoney ELSE :#{#customerRegistration.smallSumMoney} END ," +
            "a.receptionId = CASE WHEN :#{#customerRegistration.receptionId} IS NULL THEN a.receptionId ELSE :#{#customerRegistration.receptionId} END ," +
            "a.receptionName = CASE WHEN :#{#customerRegistration.receptionName} IS NULL THEN a.receptionName ELSE :#{#customerRegistration.receptionName} END ," +
            "a.areaName = CASE WHEN :#{#customerRegistration.areaName} IS NULL THEN a.areaName ELSE :#{#customerRegistration.areaName} END ," +
            "a.areaNeed = CASE WHEN :#{#customerRegistration.areaNeed} IS NULL THEN a.areaNeed ELSE :#{#customerRegistration.areaNeed} END ," +
            "a.totalNeed = CASE WHEN :#{#customerRegistration.totalNeed} IS NULL THEN a.totalNeed ELSE :#{#customerRegistration.totalNeed} END ," +
            "a.houseTypeNeed = CASE WHEN :#{#customerRegistration.houseTypeNeed} IS NULL THEN a.houseTypeNeed ELSE :#{#customerRegistration.houseTypeNeed} END ," +
            "a.visit = CASE WHEN :#{#customerRegistration.visit} IS NULL THEN a.visit ELSE :#{#customerRegistration.visit} END ," +
            "a.incomingCall = CASE WHEN :#{#customerRegistration.incomingCall} IS NULL THEN a.incomingCall ELSE :#{#customerRegistration.incomingCall} END , " +
            "a.invitation = CASE WHEN :#{#customerRegistration.invitation} IS NULL THEN a.invitation ELSE :#{#customerRegistration.invitation} END ," +
            "a.expandCustomer = CASE WHEN :#{#customerRegistration.expandCustomer} IS NULL THEN a.expandCustomer ELSE :#{#customerRegistration.expandCustomer} END ," +
            "a.paymentType = CASE WHEN :#{#customerRegistration.paymentType} IS NULL THEN a.paymentType ELSE :#{#customerRegistration.paymentType} END ," +
            "a.considerType = CASE WHEN :#{#customerRegistration.considerType} IS NULL THEN a.considerType ELSE :#{#customerRegistration.considerType} END ," +
            "a.priceAcceptance = CASE WHEN :#{#customerRegistration.priceAcceptance} IS NULL THEN a.priceAcceptance ELSE :#{#customerRegistration.priceAcceptance} END ," +
            "a.intentionPropertyType = CASE WHEN :#{#customerRegistration.intentionPropertyType} IS NULL THEN a.intentionPropertyType ELSE :#{#customerRegistration.intentionPropertyType} END ," +
            "a.refuseReason = CASE WHEN :#{#customerRegistration.refuseReason} IS NULL THEN a.refuseReason ELSE :#{#customerRegistration.refuseReason} END ," +
            "a.customerOccupation = CASE WHEN :#{#customerRegistration.customerOccupation} IS NULL THEN a.customerOccupation ELSE :#{#customerRegistration.customerOccupation} END , " +
            "a.familyStructure = CASE WHEN :#{#customerRegistration.familyStructure} IS NULL THEN a.familyStructure ELSE :#{#customerRegistration.familyStructure} END ," +
            "a.modelHouses = CASE WHEN :#{#customerRegistration.modelHouses} IS NULL THEN a.modelHouses ELSE :#{#customerRegistration.modelHouses} END ," +
            "a.customerType = CASE WHEN :#{#customerRegistration.customerType} IS NULL THEN a.customerType ELSE :#{#customerRegistration.customerType} END ," +
            "a.checkOutReason = CASE WHEN :#{#customerRegistration.checkOutReason} IS NULL THEN a.checkOutReason ELSE :#{#customerRegistration.checkOutReason} END ," +
            "a.totalContract = CASE WHEN :#{#customerRegistration.totalContract} IS NULL THEN a.totalContract ELSE :#{#customerRegistration.totalContract} END ," +
            "a.dealPaymentType = CASE WHEN :#{#customerRegistration.dealPaymentType} IS NULL THEN a.dealPaymentType ELSE :#{#customerRegistration.dealPaymentType} END ," +
            "a.moneyBack = CASE WHEN :#{#customerRegistration.moneyBack} IS NULL THEN a.moneyBack ELSE :#{#customerRegistration.moneyBack} END ," +
            "a.downPayments = CASE WHEN :#{#customerRegistration.downPayments} IS NULL THEN a.downPayments ELSE :#{#customerRegistration.downPayments} END ," +
            "a.downPaymentsMoney = CASE WHEN :#{#customerRegistration.downPaymentsMoney} IS NULL THEN a.downPaymentsMoney ELSE :#{#customerRegistration.downPaymentsMoney} END ," +
            "a.purchasePurpose = CASE WHEN :#{#customerRegistration.purchasePurpose} IS NULL THEN a.purchasePurpose ELSE :#{#customerRegistration.purchasePurpose} END ," +
            "a.paymentTypeMoney = CASE WHEN :#{#customerRegistration.paymentTypeMoney} IS NULL THEN a.paymentTypeMoney ELSE :#{#customerRegistration.paymentTypeMoney} END " +
            "where a.id = :#{#customerRegistration.id}")
    int update(@Param("customerRegistration") CustomerRegistration customerRegistration);

    @Query(value = "select * from customer_registration where is_system = 0 AND reception_id = 0 AND reception_name = 0",nativeQuery = true)
    List<CustomerRegistration> queryPublicCustomerList();

    @Query(value = "select * from customer_registration where is_system = 0 AND reception_id = 0 AND reception_name = 0 and name like %?1%",nativeQuery = true)
    List<CustomerRegistration> queryPublicCustomerListN(String name);

    @Query(value = "select * from customer_registration where is_system = 0 AND id = ?1",nativeQuery = true)
    List<CustomerRegistration> queryCustomerDetail(String customerId);


    @Query(value = "SELECT * FROM (\n" +
            "(SELECT customer_type,IFNULL(money_back,0) money_back FROM customer_registration WHERE is_system = 0 AND id = ?1) a,\n" +
            "(SELECT block_code,unit_code,doorplate_code,architecture_area,discount_total,old_total,id,IFNULL(total_actual_amount,0) FROM house_detail WHERE related_party = ?1) b\n" +
            ")",nativeQuery = true)
    List<Object> queryCustomerList(Integer customerId);

    @Query(value = "SELECT * FROM (\n" +
            "(SELECT customer_type,IFNULL(money_back,0) money_back FROM customer_registration WHERE is_system = 0 AND id = ?1) a)",nativeQuery = true)
    List<Object> queryCustomerListE(Integer customerId);

    @Modifying
    @Transactional
    @Query(value = "update customer_registration set reception_id = ?2,reception_name = ?3 where reception_id = ?1",nativeQuery = true)
    int transferUser(String inputUser, String outUser,String name);

    @Query(value = "SELECT * FROM `customer_registration` WHERE phone = ?1",nativeQuery = true)
    CustomerRegistration findFirst(String phone);

    @Query(value = "SELECT consider_type,id FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4)\n" +
            ")",nativeQuery = true)
    List<Object> qy(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT consider_type,id FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ")",nativeQuery = true)
    List<Object> qy(String sTime,String eTime,List userId);

    @Query(value = "SELECT consider_type,id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4) AND consider_type IS NOT NULL",nativeQuery = true)
    List<Object> qyW(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT consider_type,id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND consider_type IS NOT NULL",nativeQuery = true)
    List<Object> qyW(String sTime,String eTime,List userId);

    @Query(value = "SELECT consider_type,id FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?4))\n" +
            "UNION ALL\n" +
            "SELECT consider_type,id FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?4)",nativeQuery = true)
    List<Object> qyAll(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT consider_type,id FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?3))\n" +
            "UNION ALL\n" +
            "SELECT consider_type,id FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?3)",nativeQuery = true)
    List<Object> qyWRYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT consider_type,id FROM customer_registration WHERE reception_id in (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0",nativeQuery = true)
    List<Object> qyAll(String sTime,String eTime,List userId);

    @Query(value = "SELECT consider_type,id FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0",nativeQuery = true)
    List<Object> qyAllZ(String sTime,String eTime);

    @Query(value = "SELECT consider_type,id FROM customer_registration\n" +
            "WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND reception_id in (?3)",nativeQuery = true)
    List<Object> qyS(String sTime,String eTime,List idS);

    @Query(value = "SELECT consider_type,id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2",nativeQuery = true)
    List<Object> qyZ(String sTime,String eTime);

    @Query(value = "SELECT consider_type,id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3)",nativeQuery = true)
    List<Object> qyWRY(String sTime,String eTime,List types);

    @Query(value = "SELECT area_need,id num FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND reception_id IN (?3) AND `level` IN (?4)",nativeQuery = true)
    List<Object> getAreaNeedDx(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT area_need,id num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2",nativeQuery = true)
    List<Object> getAreaNeedWZ(String sTime,String eTime);

    @Query(value = "SELECT area_need,id num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3)",nativeQuery = true)
    List<Object> getAreaNeedWRY(String sTime,String eTime,List types);

    @Query(value = "SELECT area_need,id num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4) AND area_need IS NOT NULL",nativeQuery = true)
    List<Object> getAreaNeedDxW(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT area_need,id num FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND area_need IS NOT NULL",nativeQuery = true)
    List<Object> getAreaNeedDxW(String sTime,String eTime,List userId);

    @Query(value = "SELECT area_need,id FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?4))\n" +
            "UNION ALL\n" +
            "SELECT area_need,id FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?4)",nativeQuery = true)
    List<Object> getAreaNeedDxAll(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT area_need,id FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?3))\n" +
            "UNION ALL\n" +
            "SELECT area_need,id FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?3)",nativeQuery = true)
    List<Object> getAreaNeedDxWRYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT area_need,id FROM customer_registration WHERE reception_id in (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0",nativeQuery = true)
    List<Object> getAreaNeedDxAll(String sTime,String eTime,List userId);

    @Query(value = "SELECT area_need,id FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0",nativeQuery = true)
    List<Object> getAreaNeedDxAllZ(String sTime,String eTime);

    @Query(value = "SELECT total_need,id  FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND total_need IS NOT NULL",nativeQuery = true)
    List<Object> getAreaNeedDxS(String sTime,String eTime);

    @Query(value = "SELECT total_need,id  FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3) AND total_need IS NOT NULL",nativeQuery = true)
    List<Object> getAreaNeedDxWRY(String sTime,String eTime,List types);

    @Query(value = "SELECT total_need,id FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 and reception_id IN (?3) AND `level` IN (?4)",nativeQuery = true)
    List<Object> getTotalNeedDx(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT total_need,id  FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4) AND total_need IS NOT NULL",nativeQuery = true)
    List<Object> getTotalNeedDxW(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT total_need,id FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND total_need IS NOT NULL)\n" +
            "AND property_tye IN (?4))\n" +
            "UNION ALL\n" +
            "SELECT total_need,id FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND total_need IS NOT NULL\n" +
            "AND intention_property_type IN (?4)",nativeQuery = true)
    List<Object> getTotalNeedDxAll(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT total_need,id FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND total_need IS NOT NULL)\n" +
            "AND property_tye IN (?3))\n" +
            "UNION ALL\n" +
            "SELECT total_need,id FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?3) AND total_need IS NOT NULL",nativeQuery = true)
    List<Object> getTotalNeedDxWRYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT total_need,id FROM customer_registration WHERE reception_id in (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND total_need IS NOT NULL",nativeQuery = true)
    List<Object> getTotalNeedDxAll(String sTime,String eTime,List userId);

    @Query(value = "SELECT total_need,id FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 AND total_need IS NOT NULL",nativeQuery = true)
    List<Object> getTotalNeedDxAllZ(String sTime,String eTime);

    @Query(value = "SELECT IFNULL(house_type_need,'未知'),id  FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND total_need IS NOT NULL",nativeQuery = true)
    List<Object> getTotalNeedDxW(String sTime,String eTime,List userId);

    @Query(value = "SELECT IFNULL(house_type_need,'未知'),id FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 and reception_id IN (?3)",nativeQuery = true)
    List<Object> getTotalNeedDxS(String sTime,String eTime,List userId);

    @Query(value = "SELECT IFNULL(house_type_need,'未知'),id FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 and reception_id IN (?3) AND `level` IN (?4)",nativeQuery = true)
    List<Object> getHouseTypeNeedDx(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT IFNULL(house_type_need,'未知'),id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4)",nativeQuery = true)
    List<Object> getHouseTypeNeedDxW(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT IFNULL(house_type_need,'未知'),id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2",nativeQuery = true)
    List<Object> getHouseTypeNeedDxW(String sTime,String eTime,List userId);

    @Query(value = "SELECT IFNULL(house_type_need,'未知'),id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2",nativeQuery = true)
    List<Object> getHouseTypeNeedDxZ(String sTime,String eTime);

    @Query(value = "SELECT IFNULL(house_type_need,'未知'),id FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3)",nativeQuery = true)
    List<Object> getHouseTypeNeedDxWRY(String sTime,String eTime,List types);

    @Query(value = "SELECT IFNULL(house_type_need,'未知'),id FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?4))\n" +
            "UNION ALL\n" +
            "SELECT IFNULL(house_type_need,'未知'),id FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?4)",nativeQuery = true)
    List<Object> getHouseTypeNeedDxAll(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT IFNULL(house_type_need,'未知'),id FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?3))\n" +
            "UNION ALL\n" +
            "SELECT IFNULL(house_type_need,'未知'),id FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?3)",nativeQuery = true)
    List<Object> getHouseTypeNeedDxWRYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT IFNULL(house_type_need,'未知'),id FROM customer_registration WHERE reception_id in (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0",nativeQuery = true)
    List<Object> getHouseTypeNeedDxAll(String sTime,String eTime,List userId);

    @Query(value = "SELECT IFNULL(house_type_need,'未知'),id FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0",nativeQuery = true)
    List<Object> getHouseTypeNeedDxAllZ(String sTime,String eTime);

    @Query(value = "SELECT IFNULL(house_type_need,'未知'),id FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND `level` IN (?3)",nativeQuery = true)
    List<Object> getHouseTypeNeedTDx(String sTime,String eTime,List types);

    @Query(value = "SELECT IFNULL(house_type_need,'未知'),id FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 and reception_id IN (?3)",nativeQuery = true)
    List<Object> getHouseTypeNeedEDx(String sTime,String eTime,List userId);

    @Query(value = "SELECT IFNULL(house_type_need,'未知'),id FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0",nativeQuery = true)
    List<Object> getHouseTypeNeedZDx(String sTime,String eTime);

    @Query(value = "SELECT area_need,id FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND `level` IN (?3) AND area_need IS NOT NULL AND area_need != ''",nativeQuery = true)
    List<Object> getAreaNeedTDx(String sTime,String eTime,List types);

    @Query(value = "SELECT area_need,id FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND reception_id IN (?3) AND area_need IS NOT NULL AND area_need != ''",nativeQuery = true)
    List<Object> getAreaNeedEDx(String sTime,String eTime,List userId);

    @Query(value = "SELECT area_need,id FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND area_need IS NOT NULL AND area_need != ''",nativeQuery = true)
    List<Object> getAreaNeedZDx(String sTime,String eTime);

    @Query(value = "SELECT total_need,id FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND `level` IN (?3) AND total_need IS NOT NULL AND total_need != ''",nativeQuery = true)
    List<Object> getTotalNeedTDx(String sTime,String eTime,List types);

    @Query(value = "SELECT total_need,id FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 and reception_id IN (?3) AND total_need IS NOT NULL AND total_need != ''",nativeQuery = true)
    List<Object> getTotalNeedEDx(String sTime,String eTime,List userId);

    @Query(value = "SELECT total_need,id FROM customer_registration WHERE come_time BETWEEN ?1 AND ?2 AND is_system = 0 AND total_need IS NOT NULL AND total_need != ''",nativeQuery = true)
    List<Object> getTotalNeedZDx(String sTime,String eTime);

    @Query(value = "SELECT consider_type,id FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ")",nativeQuery = true)
    List<Object> getConsiderTypeCAll(String sTime,String eTime);

    @Query(value = "SELECT consider_type,id FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ")",nativeQuery = true)
    List<Object> getConsiderTypeRW(String sTime,String eTime,List ids);

    @Query(value = "SELECT consider_type,id FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?3)\n" +
            ")",nativeQuery = true)
    List<Object> getConsiderTypeWR(String sTime,String eTime,List types);

    @Query(value = "SELECT consider_type,id FROM customer_registration\n" +
            "WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND reception_id IN (?3) AND level in (?4) AND consider_type IS NOT NULL AND consider_type != ''",nativeQuery = true)
    List<Object> getConsiderTypeTDx(String sTime,String eTime,List userId,List types);

    @Query(value = "SELECT consider_type,id FROM customer_registration\n" +
            "WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND reception_id IN (?3) AND consider_type IS NOT NULL AND consider_type != ''",nativeQuery = true)
    List<Object> getConsiderTypeEDx(String sTime,String eTime,List userId);

    @Query(value = "SELECT consider_type,id FROM customer_registration\n" +
            "WHERE is_system = 0 AND come_time BETWEEN ?1 AND ?2 AND consider_type IS NOT NULL AND consider_type != ''",nativeQuery = true)
    List<Object> getConsiderTypeZDx(String sTime,String eTime);

    @Query(value = "SELECT purchase_purpose,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2" +
            " AND intention_property_type IN (?3) AND customer_type NOT IN ('小订','认筹','继续跟踪','明确拒绝') AND purchase_purpose IS NOT NULL\n" +
            "GROUP BY purchase_purpose",nativeQuery = true)
    List getPurchasePurpose(String sTime,String eTime,List types);

    @Query(value = "SELECT purchase_purpose,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2 AND reception_id in (?3) and `level` IN (?4) AND purchase_purpose IS NOT NULL\n" +
            "GROUP BY purchase_purpose",nativeQuery = true)
    List getPurchasePurposeU(String sTime,String eTime,List ids,List types);

    @Query(value = "SELECT purchase_purpose,COUNT(1) num FROM customer_registration WHERE id IN( \n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3))\n" +
            "AND property_tye IN (?4)) AND purchase_purpose IS NOT NULL AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY purchase_purpose",nativeQuery = true)
    List getPurchasePurposeUs(String sTime,String eTime,List ids,List types);

    @Query(value = "SELECT purchase_purpose,COUNT(1) num FROM customer_registration WHERE id IN( \n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3))\n" +
            ") AND purchase_purpose IS NOT NULL AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY purchase_purpose",nativeQuery = true)
    List getPurchasePurposeUs(String sTime,String eTime,List ids);

    @Query(value = "SELECT purchase_purpose,COUNT(1) FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?4) GROUP BY purchase_purpose",nativeQuery = true)
    List getPurchasePurposeW(String sTime,String eTime,List ids,List types);

    @Query(value = "SELECT purchase_purpose,COUNT(1) FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND intention_property_type IN (?3) GROUP BY purchase_purpose",nativeQuery = true)
    List getPurchasePurposeWS(String sTime,String eTime,List types);

    @Query(value = "SELECT purchase_purpose,COUNT(1) FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 GROUP BY purchase_purpose",nativeQuery = true)
    List getPurchasePurposeW(String sTime,String eTime,List ids);

    @Query(value = "SELECT a.purchase_purpose,SUM(a.num) FROM (\n" +
            "SELECT purchase_purpose,COUNT(1) num FROM customer_registration WHERE reception_id IN (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND intention_property_type in (?4) AND is_system =0 GROUP BY purchase_purpose\n" +
            "UNION ALL\n" +
            "SELECT purchase_purpose,COUNT(1) num FROM customer_registration WHERE id IN( \n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE reception_id IN (?3) AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye in (?4)) GROUP BY purchase_purpose) a GROUP BY a.purchase_purpose",nativeQuery = true)
    List getPurchasePurposeAll(String sTime,String eTime,List ids,List types);

    @Query(value = "SELECT a.purchase_purpose,SUM(a.num) FROM (\n" +
            "SELECT purchase_purpose,COUNT(1) num FROM customer_registration WHERE reception_id IN (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND intention_property_type in (?4) AND is_system =0 GROUP BY purchase_purpose\n" +
            "UNION ALL\n" +
            "SELECT purchase_purpose,COUNT(1) num FROM customer_registration WHERE id IN( \n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE reception_id IN (?3) AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye in (?4)) GROUP BY purchase_purpose) a GROUP BY a.purchase_purpose",nativeQuery = true)
    List getPurchasePurposeWYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT purchase_purpose,COUNT(1) num FROM customer_registration WHERE reception_id IN (?3) AND is_system =0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 GROUP BY purchase_purpose",nativeQuery = true)
    List getPurchasePurposeAll(String sTime,String eTime,List ids);

    @Query(value = "SELECT purchase_purpose,COUNT(1) num FROM customer_registration WHERE is_system =0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 GROUP BY purchase_purpose",nativeQuery = true)
    List getPurchasePurposeAll(String sTime,String eTime);

    @Query(value = "SELECT purchase_purpose,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0))\n" +
            "GROUP BY purchase_purpose",nativeQuery = true)
    List getPurchasePurposeCAll(String sTime,String eTime);

    @Query(value = "SELECT purchase_purpose,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND reception_id in (?3) AND is_system = 0))\n" +
            "GROUP BY purchase_purpose",nativeQuery = true)
    List getPurchasePurposeRW(String sTime,String eTime,List ids);

    @Query(value = "SELECT purchase_purpose,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?4))\n" +
            "GROUP BY purchase_purpose",nativeQuery = true)
    List getPurchasePurposeWR(String sTime,String eTime,List types);

    @Query(value = "SELECT purchase_purpose,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND reception_id in (?3) AND is_system = 0)\n" +
            "AND property_tye IN (?4))\n" +
            "GROUP BY purchase_purpose",nativeQuery = true)
    List getCustomerRegistrationRepositoryRWAll(String sTime,String eTime,List ids,List types);

    @Query(value = "SELECT purchase_purpose,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2 and `level` IN (?3) AND purchase_purpose IS NOT NULL\n" +
            "GROUP BY purchase_purpose",nativeQuery = true)
    List getPurchasePurposeT(String sTime,String eTime,List types);

    @Query(value = "SELECT purchase_purpose,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2 and reception_id IN (?3) AND purchase_purpose IS NOT NULL\n" +
            "GROUP BY purchase_purpose",nativeQuery = true)
    List getPurchasePurposeY(String sTime,String eTime,List ids);

    @Query(value = "SELECT purchase_purpose,COUNT(1) FROM customer_registration WHERE customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 GROUP BY purchase_purpose",nativeQuery = true)
    List getPurchasePurposeSE(String sTime,String eTime);

    @Query(value = "SELECT IFNULL(a.`level`,'Z'),COUNT(1) num FROM (SELECT `level` FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3))\n" +
            "AND property_tye IN (?4)) AND `level` IS NOT NULL AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0) a\n" +
            "GROUP BY a.`level`",nativeQuery = true)
    List getUsrIntentionalityCj(String sTime,String eTime,List ids,List types);

    @Query(value = "SELECT IFNULL(a.`level`,'Z'),COUNT(1) num FROM (SELECT `level` FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房'))\n" +
            ") AND `level` IS NOT NULL AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0) a\n" +
            "GROUP BY a.`level`",nativeQuery = true)
    List getUsrIntentionalityCj(String sTime,String eTime);

    @Query(value = "SELECT IFNULL(a.`level`,'Z'),COUNT(1) num FROM (SELECT `level` FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房'))\n" +
            "AND property_tye IN (?3)) AND `level` IS NOT NULL AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0) a\n" +
            "GROUP BY a.`level`",nativeQuery = true)
    List getUsrIntentionalityCjWRY(String sTime,String eTime,List types);

    @Query(value = "SELECT IFNULL(a.`level`,'Z'),COUNT(1) num FROM (SELECT `level` FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3))\n" +
            ") AND `level` IS NOT NULL AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0) a\n" +
            "GROUP BY a.`level`",nativeQuery = true)
    List getUsrIntentionalityCj(String sTime,String eTime,List ids);

    @Query(value = "SELECT IFNULL(a.`level`,'Z'),COUNT(1) num FROM customer_registration a \n" +
            "WHERE a.customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND a.reception_id IN (?3)  AND a.intention_property_type IN (?4)\n" +
            "AND a.come_time >= ?1\n" +
            "AND a.come_time <= ?2\n" +
            "GROUP BY a.`level`",nativeQuery = true)
    List getUsrIntentionalityWCj(String sTime,String eTime,List ids,List types);

    @Query(value = "SELECT IFNULL(a.`level`,'Z'),COUNT(1) num FROM customer_registration a \n" +
            "WHERE a.customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND a.come_time >= ?1\n" +
            "AND a.come_time <= ?2\n" +
            "GROUP BY a.`level`",nativeQuery = true)
    List getUsrIntentionalityWCj(String sTime,String eTime);

    @Query(value = "SELECT IFNULL(a.`level`,'Z'),COUNT(1) num FROM customer_registration a \n" +
            "WHERE a.customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND a.reception_id IN (?3)\n" +
            "AND a.come_time >= ?1\n" +
            "AND a.come_time <= ?2\n" +
            "GROUP BY a.`level`",nativeQuery = true)
    List getUsrIntentionalityWCj(String sTime,String eTime,List ids);

    @Query(value = "SELECT IFNULL(a.`level`,'Z'),COUNT(1) num FROM customer_registration a \n" +
            "WHERE a.customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND a.intention_property_type IN (?3)\n" +
            "AND a.come_time >= ?1\n" +
            "AND a.come_time <= ?2\n" +
            "GROUP BY a.`level`",nativeQuery = true)
    List getUsrIntentionalityWCjWRY(String sTime,String eTime,List types);

    @Query(value = "SELECT IFNULL(a.`level`,'Z'),SUM(a.num) FROM (\n" +
            "SELECT a.`level`,COUNT(1) num FROM (SELECT `level` FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3))\n" +
            "AND property_tye IN (?4)) AND `level` IS NOT NULL AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0) a\n" +
            "GROUP BY a.`level`\n" +
            "UNION ALL\n" +
            "SELECT IFNULL(a.`level`,'Z'),COUNT(1) num FROM customer_registration a \n" +
            "WHERE a.customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND a.reception_id IN (?3)  AND a.intention_property_type IN (?4)\n" +
            "AND a.come_time >= ?1\n" +
            "AND a.come_time <= ?2\n" +
            "GROUP BY a.`level`\n" +
            ")  a GROUP BY a.`level`",nativeQuery = true)
    List getUsrIntentionalityAll(String sTime,String eTime,List ids,List types);

    @Query(value = "SELECT IFNULL(a.`level`,'Z'),SUM(a.num) FROM (\n" +
            "SELECT a.`level`,COUNT(1) num FROM (SELECT `level` FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房'))\n" +
            "AND property_tye IN (?3)) AND `level` IS NOT NULL AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0) a\n" +
            "GROUP BY a.`level`\n" +
            "UNION ALL\n" +
            "SELECT IFNULL(a.`level`,'Z'),COUNT(1) num FROM customer_registration a \n" +
            "WHERE a.customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND a.intention_property_type IN (?3)\n" +
            "AND a.come_time >= ?1\n" +
            "AND a.come_time <= ?2\n" +
            "GROUP BY a.`level`\n" +
            ")  a GROUP BY a.`level`",nativeQuery = true)
    List getUsrIntentionalityWRYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT IFNULL(a.`level`,'Z'),SUM(a.num) FROM (\n" +
            "SELECT a.`level`,COUNT(1) num FROM (SELECT `level` FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房'))\n" +
            ") AND `level` IS NOT NULL AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0) a\n" +
            "GROUP BY a.`level`\n" +
            "UNION ALL\n" +
            "SELECT IFNULL(a.`level`,'Z'),COUNT(1) num FROM customer_registration a \n" +
            "WHERE a.customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND a.come_time >= ?1\n" +
            "AND a.come_time <= ?2\n" +
            "GROUP BY a.`level`\n" +
            ")  a GROUP BY a.`level`",nativeQuery = true)
    List getUsrIntentionalityAll(String sTime,String eTime);

    @Query(value = "SELECT IFNULL(a.`level`,'Z'),SUM(a.num) FROM (\n" +
            "SELECT a.`level`,COUNT(1) num FROM (SELECT `level` FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3))\n" +
            ") AND `level` IS NOT NULL AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0) a\n" +
            "GROUP BY a.`level`\n" +
            "UNION ALL\n" +
            "SELECT IFNULL(a.`level`,'Z'),COUNT(1) num FROM customer_registration a \n" +
            "WHERE a.customer_type IN('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND a.reception_id IN (?3)\n" +
            "AND a.come_time >= ?1\n" +
            "AND a.come_time <= ?2\n" +
            "GROUP BY a.`level`\n" +
            ")  a GROUP BY a.`level`",nativeQuery = true)
    List getUsrIntentionalityAll(String sTime,String eTime,List ids);


    @Modifying
    @Transactional
    @Query(value = "update customer_registration set large_sum = 0,large_sum_money = 0,small_sum = 0,small_sum_money = 0,down_payments = 0,down_payments_money = 0,payment_type = '',payment_type_money = 0" +
            " where id = ?1",nativeQuery = true)
    int updateRc(Integer id);


    @Modifying
    @Transactional
    @Query(value = "update customer_registration set identify = 0,identify_money = 0,large_sum = 0,large_sum_money = 0,down_payments = 0,down_payments_money = 0,payment_type = '',payment_type_money = 0" +
            " where id = ?1",nativeQuery = true)
    int updateXd(Integer id);


    @Modifying
    @Transactional
    @Query(value = "update customer_registration set identify = 0,identify_money = 0,small_sum = 0,small_sum_money = 0,down_payments = 0,down_payments_money = 0,payment_type = '',payment_type_money = 0" +
            " where id = ?1",nativeQuery = true)
    int updateDd(Integer id);

    @Modifying
    @Transactional
    @Query(value = "update customer_registration set large_sum = 0,large_sum_money = 0,small_sum = 0,small_sum_money = 0,identify = 0,identify_money = 0,payment_type = '',payment_type_money = 0" +
            " where id = ?1",nativeQuery = true)
    int updateSf(Integer id);

    @Modifying
    @Transactional
    @Query(value = "update customer_registration set large_sum = 0,large_sum_money = 0,small_sum = 0,small_sum_money = 0,down_payments = 0,down_payments_money = 0,identify = 0,identify_money = 0" +
            " where id = ?1",nativeQuery = true)
    int updateQk(Integer id);

    @Query(value = "SELECT customer_source,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND property_tye IN (?4)) \n" +
            ") GROUP BY customer_source",nativeQuery = true)
    List getCustomerSource(String sTime,String eTime,List receptionId,List types);


    @Query(value = "SELECT customer_source,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2) \n" +
            ") GROUP BY customer_source",nativeQuery = true)
    List getCustomerSource(String sTime,String eTime,List receptionId);

    @Query(value = "SELECT customer_source,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND reception_id IN (?3) AND customer_type IN ('小订','认筹','继续跟踪','明确拒绝','已退房') AND customer_source IS NOT NULL\n" +
            "GROUP BY customer_source",nativeQuery = true)
    List getCustomerSourceM(String sTime,String eTime,List receptionId);

    @Query(value = "SELECT customer_source,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND reception_id IN (?3) AND customer_type NOT IN ('小订','认筹','继续跟踪','明确拒绝','已退房') AND intention_property_type IN (?4) AND customer_source IS NOT NULL\n" +
            "GROUP BY customer_source",nativeQuery = true)
    List getCustomerSourceM(String sTime,String eTime,List receptionId,List types);

    @Query(value = "SELECT a.customer_source,SUM(a.num) FROM(\n" +
            "SELECT customer_source,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?4))\n" +
            "GROUP BY customer_source\n" +
            "UNION ALL\n" +
            "SELECT customer_source,COUNT(1) num FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?4) GROUP BY customer_source\n" +
            ") a GROUP BY a.customer_source",nativeQuery = true)
    List getCustomerSourceAll(String sTime,String eTime,List receptionId,List types);

    @Query(value = "SELECT a.customer_source,SUM(a.num) FROM(\n" +
            "SELECT customer_source,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?3))\n" +
            "GROUP BY customer_source\n" +
            "UNION ALL\n" +
            "SELECT customer_source,COUNT(1) num FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?3) GROUP BY customer_source\n" +
            ") a GROUP BY a.customer_source",nativeQuery = true)
    List getCustomerSourceWRYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT customer_source,COUNT(1) FROM customer_registration WHERE reception_id in (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY customer_source",nativeQuery = true)
    List getCustomerSourceAll(String sTime,String eTime,List receptionId);

    @Query(value = "SELECT customer_source,COUNT(1) FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY customer_source",nativeQuery = true)
    List getCustomerSourceAllZ(String sTime,String eTime);

    @Query(value = "SELECT customer_source,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2) \n" +
            ") GROUP BY customer_source",nativeQuery = true)
    List getCustomerSourceSE(String sTime,String eTime);

    @Query(value = "SELECT customer_source,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND customer_type NOT IN ('小订','认筹','继续跟踪','明确拒绝','已退房') AND customer_source IS NOT NULL\n" +
            "GROUP BY customer_source",nativeQuery = true)
    List getCustomerSourceWSE(String sTime,String eTime);

    @Query(value = "SELECT customer_source,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND customer_type NOT IN ('小订','认筹','继续跟踪','明确拒绝','已退房') AND intention_property_type IN (?3) AND customer_source IS NOT NULL\n" +
            "GROUP BY customer_source",nativeQuery = true)
    List getCustomerSourceWRY(String sTime,String eTime,List types);

    @Query(value = "SELECT customer_source,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND property_tye IN (?3)) \n" +
            ") GROUP BY customer_source",nativeQuery = true)
    List getCustomerSourceWR(String sTime,String eTime,List types);

    @Query(value = "SELECT refuse_reason,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND property_tye IN (?4))\n" +
            ") GROUP BY refuse_reason",nativeQuery = true)
    List getRefuseReason(String sTime,String eTime,List receptionId,List types);

    @Query(value = "SELECT refuse_reason,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 )\n" +
            ") GROUP BY refuse_reason",nativeQuery = true)
    List getRefuseReason(String sTime,String eTime,List receptionId);

    @Query(value = "SELECT refuse_reason,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND reception_id IN (?3) AND customer_type IN ('小订','认筹','继续跟踪','明确拒绝','已退房') AND refuse_reason IS NOT NULL\n" +
            "GROUP BY refuse_reason",nativeQuery = true)
    List getRefuseReasonM(String sTime,String eTime,List receptionId);

    @Query(value = "SELECT refuse_reason,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND reception_id IN (?3) AND customer_type IN ('小订','认筹','继续跟踪','明确拒绝','已退房') AND intention_property_type IN (?4) AND refuse_reason IS NOT NULL\n" +
            "GROUP BY refuse_reason",nativeQuery = true)
    List getRefuseReasonM(String sTime,String eTime,List receptionId,List types);

    @Query(value = "SELECT a.refuse_reason,SUM(a.num) FROM(\n" +
            "SELECT refuse_reason,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?4))\n" +
            "GROUP BY refuse_reason\n" +
            "UNION ALL\n" +
            "SELECT refuse_reason,COUNT(1) num FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?4) GROUP BY refuse_reason\n" +
            ") a GROUP BY a.refuse_reason",nativeQuery = true)
    List getRefuseReasonAll(String sTime,String eTime,List receptionId,List types);

    @Query(value = "SELECT a.refuse_reason,SUM(a.num) FROM(\n" +
            "SELECT refuse_reason,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?3))\n" +
            "GROUP BY refuse_reason\n" +
            "UNION ALL\n" +
            "SELECT refuse_reason,COUNT(1) num FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?3) GROUP BY refuse_reason\n" +
            ") a GROUP BY a.refuse_reason",nativeQuery = true)
    List getRefuseReasonWRYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT refuse_reason,COUNT(1) FROM customer_registration WHERE reception_id in (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY refuse_reason",nativeQuery = true)
    List getRefuseReasonAll(String sTime,String eTime,List receptionId);

    @Query(value = "SELECT refuse_reason,COUNT(1) FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY refuse_reason",nativeQuery = true)
    List getRefuseReasonAllZ(String sTime,String eTime);

    @Query(value = "SELECT refuse_reason,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY refuse_reason",nativeQuery = true)
    List getRefuseReasonSE(String sTime,String eTime);

    @Query(value = "SELECT refuse_reason,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND customer_type IN ('小订','认筹','继续跟踪','明确拒绝','已退房') AND refuse_reason IS NOT NULL\n" +
            "GROUP BY refuse_reason",nativeQuery = true)
    List getRefuseReasonWSE(String sTime,String eTime);

    @Query(value = "SELECT refuse_reason,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND customer_type IN ('小订','认筹','继续跟踪','明确拒绝','已退房') AND intention_property_type IN (?3) AND refuse_reason IS NOT NULL\n" +
            "GROUP BY refuse_reason",nativeQuery = true)
    List getRefuseReasonWRY(String sTime,String eTime,List types);

    @Query(value = "SELECT refuse_reason,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND property_tye IN (?3))\n" +
            ") GROUP BY refuse_reason",nativeQuery = true)
    List getRefuseReasonWR(String sTime,String eTime,List types);

    @Query(value = "SELECT model_houses,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            "AND property_tye IN (?4))" +
            "GROUP BY model_houses",nativeQuery = true)
    List getModelHouses(String sTime,String eTime,List receptionId,List types);

    @Query(value = "SELECT model_houses,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id  IN (?3) AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2))\n" +
            "GROUP BY model_houses",nativeQuery = true)
    List getModelHouses(String sTime,String eTime,List receptionId);

    @Query(value = "SELECT model_houses,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND reception_id IN (?3) AND customer_type IN ('小订','认筹','继续跟踪','明确拒绝','已退房') AND intention_property_type IN (?4) AND model_houses IS NOT NULL\n" +
            "GROUP BY model_houses",nativeQuery = true)
    List getModelHousesM(String sTime,String eTime,List receptionId,List types);

    @Query(value = "SELECT model_houses,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND reception_id IN (?3) AND customer_type IN ('小订','认筹','继续跟踪','明确拒绝','已退房') AND model_houses IS NOT NULL\n" +
            "GROUP BY model_houses",nativeQuery = true)
    List getModelHousesM(String sTime,String eTime,List receptionId);

    @Query(value = "SELECT a.model_houses,SUM(a.num) FROM(\n" +
            "SELECT model_houses,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?4))\n" +
            "GROUP BY model_houses\n" +
            "UNION ALL\n" +
            "SELECT model_houses,COUNT(1) num FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND reception_id in (?3) AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?4) GROUP BY model_houses\n" +
            ") a GROUP BY a.model_houses",nativeQuery = true)
    List getModelHousesAll(String sTime,String eTime,List receptionId,List types);

    @Query(value = "SELECT a.model_houses,SUM(a.num) FROM(\n" +
            "SELECT model_houses,COUNT(1) num FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type\n" +
            "NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0)\n" +
            "AND property_tye IN (?3))\n" +
            "GROUP BY model_houses\n" +
            "UNION ALL\n" +
            "SELECT model_houses,COUNT(1) num FROM customer_registration WHERE customer_type IN ('小订','继续跟踪','认筹','明确拒绝','已退房')\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0\n" +
            "AND intention_property_type IN (?3) GROUP BY model_houses\n" +
            ") a GROUP BY a.model_houses",nativeQuery = true)
    List getModelHousesWRYAll(String sTime,String eTime,List types);

    @Query(value = "SELECT model_houses,COUNT(1) FROM customer_registration WHERE reception_id in (?3)\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY model_houses",nativeQuery = true)
    List getModelHousesAll(String sTime,String eTime,List receptionId);

    @Query(value = "SELECT model_houses,COUNT(1) FROM customer_registration WHERE come_time >= ?1 AND come_time <= ?2 AND is_system = 0 GROUP BY model_houses",nativeQuery = true)
    List getModelHousesAllZ(String sTime,String eTime);

    @Query(value = "SELECT model_houses,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2)\n" +
            ") GROUP BY model_houses",nativeQuery = true)
    List getModelHousesSE(String sTime,String eTime);

    @Query(value = "SELECT model_houses,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND customer_type IN ('小订','认筹','继续跟踪','明确拒绝','已退房') AND model_houses IS NOT NULL\n" +
            "GROUP BY model_houses",nativeQuery = true)
    List getModelHousesWSE(String sTime,String eTime);

    @Query(value = "SELECT model_houses,COUNT(1) FROM customer_registration WHERE is_system = 0 AND come_time >= ?1 AND come_time <= ?2\n" +
            "AND customer_type IN ('小订','认筹','继续跟踪','明确拒绝','已退房') AND intention_property_type IN (?3) AND model_houses IS NOT NULL\n" +
            "GROUP BY model_houses",nativeQuery = true)
    List getModelHousesWRY(String sTime,String eTime,List types);

    @Query(value = "SELECT model_houses,COUNT(1) FROM customer_registration WHERE id IN(\n" +
            "SELECT related_party  FROM house_detail WHERE related_party IN(\n" +
            "SELECT id FROM customer_registration WHERE customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') AND is_system = 0\n" +
            "AND come_time >= ?1 AND come_time <= ?2 AND property_tye IN (?3))\n" +
            ") GROUP BY model_houses",nativeQuery = true)
    List getModelHousesWR(String sTime,String eTime,List types);

    @Query(value = "select  count(1) from customer_registration where virtual_number = ?1",nativeQuery = true)
    int countPhone(String phone);

    @Query(value = "select virtual_number from customer_registration WHERE customer_type IN ('小订','认筹','继续跟踪','明确拒绝','已退房') AND phone LIKE '%****%'",nativeQuery = true)
    List<String> fCustomer();

    @Modifying
    @Transactional
    @Query(value = "delete from customer_registration where virtual_number = ?1",nativeQuery = true)
    int deleteFCustomer (String xnh);

    @Query(value = "select COUNT(1) from customer_registration WHERE phone = ?1 AND customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房')",nativeQuery = true)
    int queryQH (String phone);

    @Query(value = "select customer_source from customer_registration WHERE id = ?1 AND customer_source = '渠道'",nativeQuery = true)
    String findBb(Integer id);

    @Query(value = "select virtual_number from customer_registration WHERE id = ?1 AND customer_source = '渠道'",nativeQuery = true)
    String findvirtualNumber(Integer id);

    @Query(value = "select count(1) from customer_registration WHERE virtual_number = ?1",nativeQuery = true)
    int findVirtualNumber(String virtualNumber);
}
